Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scottpt
Posting Yak Master
186 Posts |
 Posted - 2004-08-12 : 14:16:59
|
| Trying to dynamically set the path name in a Destination Text object. Getting a "TYPE MISMATCH" error when I use a variable name in the objPackage.Connections({VarName}).DataSource Does any know a way aroung this?++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Function Main() ' First of all get the Package handle Set objPackage = DTSGlobalVariables.parent Dim sServer, sUID, sPWD dim conn dim rs dim strSQL dim conStr Dim state set rs = CreateObject("ADODB.recordset") set conn = CreateObject("ADODB.Connection") ' Assign parameters sServer = DTSGlobalVariables("ServerName").Value sUID = DTSGlobalVariables("UserName").Value sPWD = DTSGlobalVariables("UserPassword").Value conStr="Driver={SQL Server};Server=" & sServer & ";UID=" & sUID & ";pwd=" & sPWD conn.ConnectionString=constr conn.CursorLocation=2 conn.open strSQL = "Select StateAbbr,Path from stmdss01..ImportPath where type='STM_CP_INPUT_UNLOAD' " rs.Open strSQL, conn DO until rs.eof ' Set the Source File state=rs("StateAbbr") objPackage.Connections( state).DataSource = rs("path") rs.movenext loop Dim i For i = 1 To objPackage.Connections.Count If objPackage.Connections(i).ProviderID = "SQLOLEDB.1" Then objPackage.Connections(i).DataSource = sServer objPackage.Connections(i).Password = sPWD objPackage.Connections(i).UserID = sUID End If Next Main = DTSTaskExecResult_SuccessEnd Function |
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2004-08-12 : 14:35:34
|
| SOLUTION:Loop trough till the name matches the text.' Set the Source FileFor i = 1 To objPackage.Connections.Count If trim(objPackage.Connections(i).Name) = rs("StateAbbr") Then objPackage.Connections(i).DataSource = rs("path") End IfNext |
 |
|
|
|
|
|
Active X dynamically setting paths.