Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Looping in DTS

Posted on 2006-03-23
7
Medium Priority
?
2,086 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
0
Comment
Question by:adwiseman
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:udayshankar
ID: 16274623
0
 
LVL 14

Author Comment

by:adwiseman
ID: 16275070
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16279841
>>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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 14

Author Comment

by:adwiseman
ID: 16280241
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16280446
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
 
LVL 14

Author Comment

by:adwiseman
ID: 16280595
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
 
LVL 14

Author Comment

by:adwiseman
ID: 16280602
DestMDB is empty after my last loop
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question