asked on

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

udayshankar

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

adwiseman

ASKER

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

adwiseman

ASKER

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.

Anthony Perkins

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

adwiseman

ASKER

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

adwiseman

ASKER

DestMDB is empty after my last loop