Looping in DTS

I have a DTS Package THAT Loops
The function that causes it too loop is at the end of this post.
In part of my loop, I change the datasource to a different access database each time I loop, but the connection never changes.  I've experienced this before, and worked around it by executing a second dTS package that did the datapump.  Has anyone else tried this.  It appears that once I use a connection, I can not change that connections datasource.

How do you change the datasource of a connection after you have used that connection in the DTS package.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
DIM oPkg
Set oPkg = DTSGlobalVariables.Parent

IF DTSGlobalVariables("DestMDB").Value <> "" THEN
      Dim oConn

      Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Access Copy")
      oConn.DataSource = DTSGlobalVariables("DestMDB").Value

      Set oConn = Nothing

      oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus  = DTSStepExecStat_Waiting
      oPkg.Steps("DTSStep_DTSExecuteSQLTask_4").ExecutionStatus  = DTSStepExecStat_Waiting
      oPkg.Steps("DTSStep_DTSDynamicPropertiesTask_2").ExecutionStatus  = DTSStepExecStat_Waiting
      oPkg.Steps("DTSStep_DTSDataPumpTask_2").ExecutionStatus  = DTSStepExecStat_Waiting

      oPkg.Steps("DTSStep_DTSActiveScriptTask_4").ExecutionStatus  = DTSStepExecStat_Waiting
      oPkg.Steps("DTSStep_DTSExecuteSQLTask_5").ExecutionStatus  = DTSStepExecStat_Waiting
   Main = DTSStepScriptResult_DontExecuteTask

Else
      Main = DTSTaskExecResult_Success
 END IF

      Main = DTSTaskExecResult_Success

End Function
Start Free Trial