Dynamic Task properties for changing connections

[ReportDr00]

Hello

I am having trouble changing connection parameter dynamically for source connection.
I am not finding much info or example on changing connections dynamically
any help is greatly appreciated, if i find a sample .dts it would great.
i have the change connections of source after successful completion of transform data task. there are about 15 odbc databases and i am trying to gather information from all these one by one and dump it in destination sql server table.

help please....

 

[Sweenster]

Sounds like you need to loop and set the connection properties each time using a Dynamic Properties Task.
I found

http://www.sqldts.com

had some great examples when I was learning how to do this..

Check out Looping, Importing and Archiving

http://www.sqldts.com/246.aspx

You can download sample pkg at the bottom of the page.

Another example of a simple loop in DTS

http://www.sqldts.com/redir.aspx?1295

Good Luck!

Mark

 

[ReportDr00]

I did used Looping, Importing and Archiving

http://www.sqldts.com/246.aspx

as my start up point and i was able to modify it to my requirements.
Where i am stuck is the active X script task (Begin loop) and other tasks after that does not run.
Below are the tasks i have
Define Table Test Task
Exisiting Connection = SQL Server Destination
SQL Statement
(Create Table Test (ID varchar(10) Not Null,client varchar(50) Null,datebirth datetime Null,gender varchar(1) null,ssn varchar(11) null)

On Success of above task

ActiveXScript Task 1 - Begin Loop

Option Explicit

Function Main()
' Declare Variables
dim pkg
dim stpEnterLoop
dim stpFinished
Dim conOthSource

' Get Package Object
set pkg = DTSGlobalVariables.Parent

set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_3")

set conOthSource= pkg.Connections("SOURCECONN")

'gv_ConnName,gv_ConnCount

if ShouldILoop = True then


stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True

conOthSource.DataSource=DTSGlobalVariables("gv_ConnName").Value

stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success

End Function



Function ShouldILoop

dim pkg
Dim counter
int counter
counter=0
set pkg = DTSGlobalVariables.Parent
counter = DTSGlobalVariables("gv_ConnCount").Value

'So long as there is more than 1 file carry on

if CInt( counter) <= 10 then

DTSGlobalVariables("gv_ConnCount").Value = CInt(counter)+1
DTSGlobalVariables("gv_ConnName").Value = "LOCALSYS"
ShouldILoop = CBool(True)

else
ShouldILoop = CBool(False)
End if

End Function

On Success of ActiveXScript Task 1 with in loop

perform transform data task between source and destination (sql server table created at first task)

On Success of Transform Data Task perform ActiveX Script Task Loop Around

Option Explicit

Function Main()

dim pkg
dim stpbegin

set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

'The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting

stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function

On Success of ActiveX Script Task 1 begin loop and loop ends perform ActiveX Script Task 3 Finished

Option Explicit

Function Main()

MSGBOX "Package has Completed."

Main = DTSTaskExecResult_Success
End Function


Only define table task runs to complete all others says Not Run and i am not able to figure out the cause
i really appreciate any help

Armani

 

[ReportDr00]

Hello again,

I got my loop working and i added Dynamic properties which set the value of datasource using global variable which gets reset in the active x script task begin loop.
The only problem i am having is the source datasource is not changing, it is running the query for the same database multiple times. If i get to switch the database i am good to go.
Come on i came this far, a little push i should be able to cross the bridge....i really appreciate your feedback and answer.
i might be missing something that i dont know, i have message box to see what is the datasource everytime it changes in the active x script task begin loop and it does changes, but this is not reflecting on the transform data task or data pump task, the connection is set to the first server and database and the query is run on the same server and database every time it goes in loop.

please help me.....

Armani