"Kayda" wrote:

> Hi:
>
> I have posted concerning this-I don't mean to double post but I have
> isolated the problem and have new information. (and I'm desperate!!)
>
> I am trying set the data connection object (ODBC to Sybase) twice in
> the same package but the Data Pump Tasks that use this connection don't
> seem to be using the new setting in the connection after the second
> time it is set, although at the end of the package I can see that the
> connection has been set. The pumps just use the original connection a
> second time instead of using the 2nd connection.
>
> Here is the progress:
> 1. SQL Task1: Gets server information from a SQL Server table, and sets
> it to a recordset global variable in the DTS.
> 2. ActiveX Task1: Begins loop, and sets the connection to server2 and
> sets sql of datapump tasks
> 3. Several data pumps run.
> 4. ActiveX Task2: just loops around to ActiveX Task1
> 5. ActiveX Task1: Sets the connection object to server2 (msgbox tells
> me this happens successfully)
> 6. The datapumps continue to use server2(??)
> 7. Package finishes succesfully
> 8. I right click on first datapump and do a "execute task". It executes
> using server2, which for some reason it did not do during execution of
> the entire package.
>
> The loops works fine (as I have gotten it from you Allan), it clearly
> goes through twice when I have specified two servers (I can see the
> data in the resulting table is doubled from the source server, and I
> can see it in the progress window going through the data pump steps
> twice). After, if I run the data pump tasks by right clicking and doing
> an "execute task" then it works using server2 was set.
>
> I put a msgbox in the ActiveX Task1 to confirm the server is being set
> correctly.
>
> Any ideas here? Is there some other property I need to set? Is it a
> setting with the Data Pump task? This all seems very weird-I feel like
> I must be missing something obvious.
>
> Here is the part of the code of ActiveX Task 1 (only part) that sets
> the connection (let me know if you want to see the package):
>
> Dim pkg
> Dim SCCScon
> Dim objRS
> Dim SQLStatement
>
> set pkg = DTSGlobalVariables.Parent
> set SCCScon = pkg.Connections("Connection_Name")
>
> set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
> set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
>
> Set objRS = DTSGlobalVariables("gvDataSources").Value
>
>
> stpFinished.DisableStep = true
> stpEnterLoop.DisableStep = false
> stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
>
> msgbox "DSN is now: " & SCCScon.DataSource
>
> SCCScon.DataSource = objRS.Fields(1)
> SCCScon.UserID = objRS.Fields(2)
> SCCScon.Password = objRS.Fields(3)
>
> msgbox "Now I've set the DSN and it is now: " & SCCScon.DataSource
>
> _____________________________________________
>
> Thanks as always,
> Kayda
>