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
LVL 14
adwisemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

udayshankarCommented:
0
adwisemanAuthor Commented:
I know how to make my DTS loop, I posted the code.

I need to change the database connection each time a loop to a different database, and I need it to actualy change the connection.  Traditional methonds of driving the connection using a global variable only works the first time I set it.  Once I use the connection just once in the DTS package, I can not change the connection again.
0
Anthony PerkinsCommented:
>>Has anyone else tried this.<<
We use it all the time.  Although admittedly we do not use MS Access connections, but rather text connections.  Are you sure the code is getting executed the second time around? I am going to guess that it is not.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

adwisemanAuthor Commented:
Found it.

There is a property of the Access Connection called Reusable = -1 by default.  This causes the connection to be cached and does not change once established even if you change the datasource.  Set Reusable = 0, and the connection will refresh when it's datasource is changed.
0
Anthony PerkinsCommented:
Great and thanks for the admittedly not deserved points.

Totally unrelated, but you can simplify and check for failure in your code as follows:

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.Connections("Microsoft Access Copy").DataSource = DTSGlobalVariables("DestMDB").Value
      
      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
      Main = DTSTaskExecResult_Success
Else
      ' Main = DTSTaskExecResult_Success
      Main = DTSTaskExecResult_Failure
END IF

' Main = DTSTaskExecResult_Success

End Function
0
adwisemanAuthor Commented:
It works like this

IF DTSGlobalVariables("DestMDB").Value <> "" THEN
    setup for looping and start the loop,
    also flag myself as not executed, so the steps after my loop don't begin.
ELSE
    indicating I've executed the last loop, and I should not loop anymore
    Set my results to success so the steps to folow will begin
0
adwisemanAuthor Commented:
DestMDB is empty after my last loop
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.