I did used Looping, Importing and Archiving
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
