We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Looping in DTS

adwiseman
adwiseman asked
on
Medium Priority
2,140 Views
Last Modified: 2013-11-30
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
Comment
Watch Question

Author

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.
CERTIFIED EXPERT
Top Expert 2012
Commented:
>>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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

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

Author

Commented:
DestMDB is empty after my last loop
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.