DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-04 : 17:29:29

Brian writes "Ok Team,

I think I got one for ya. The code snippet below shows the method I am trying to use to access and execute a DTS package on the server. My problem is neither getting access to the package or executing it but I included the code to be a dork. The problem is the order the steps are executed in when I run the code as opposed to when I run the package through Enterprise Mangler. On the server everything runs fine, perfect order, no errors. Running it through the code, some of my steps are executed out of order. Also as a note, If I were to delete a step in design mode, and then add it back and save, running the code puts that step at the end of the sequence of steps. Errrr. Very frustrating considering that the package has almost 50 steps in it. Have any of you ever come across this?

using:
Sql Server 7.0 SP2
VB6 SP4
Windows 2000
SQLDMO

Thanks for any help,

Brian"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-04 : 18:02:34

Hmmmm. I don't see any code.

I've had the same thing happen as far as deleting a step and then adding it back; DTS seems to maintain a step order based on when it was created, not where it is placed in the process. I agree, very much a pain in the ass!

Have you tried deleting the steps, adding them back, then saving it as a new package? This worked for me a few times and put them in the right order, but I haven't tested it recently to see if it still works.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-04 : 18:10:53

I believe that DTS allows some steps to be run simultaneously unless there are specific OnCompletion, OnSuccess or OnFailure controls. These are the only way I know of to force DTS to run things in a specific order.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

BMurray4321
Starting Member

4 Posts

Posted - 2002-01-07 : 11:10:12

This was an email sent to the team, the Code must have gotten cutoff. Repost of original:

I think I got one for ya. The code snippet below shows the method I am trying to use to access and execute a DTS package on the server. My problem is neither getting access to the package or executing it but I included the code to be a dork. The problem is the order the steps are executed in when I run the code as opposed to when I run the package through Enterprise Mangler. On the server everything runs fine, perfect order, no errors. Running it through the code, some of my steps are executed out of order. Also as a note, If I were to delete a step in design mode, and then add it back and save, running the code puts that step at the end of the sequence of steps. Errrr. Very frustrating considering that the package has almost 50 steps in it. Have any of you ever come across this?

using:
Sql Server 7.0 SP2
VB6 SP4
Windows 2000
SQLDMO

Thanks for any help,

Brian Murray

Private WithEvents mobjDTSPackage As DTS.Package

Dim strSource As String
Dim strDescription As String
Dim intStepExecResult As DTSStepExecResult

mobjDTSPackage.LoadFromSQLServer ServerName:=mstrServerName, _
ServerUserName:=mstrUserName, ServerPassword:=mstrPassword, _
Packagename:=mstrDTSPackageName, _
PackagePassword:=mstrPackagePassword

With mobjDTSPackage
intStepCount = .Steps.Count
For i = 1 To intStepCount
.Steps(i).ExecuteInMainThread = True
.Steps(i).Execute
Next
.SaveToSQLServer ServerName:=mstrServerName, _
End With

BMurray4321
Starting Member

4 Posts

Posted - 2002-01-07 : 12:38:44

and so to answer my own question, the method that can be used is to change the description of each step to be the process order number. Then within your For...Next loop just keep checking for the next step in order and execute it. Dont forget to reset your counter after each execution. Example:

Dim DTSIndex as string

For i = 1 To intStepCount
If DTSIndex = Trim(.Steps(i).Description) Then
.Steps(i).ExecuteInMainThread = True
.Steps(i).Execute
End If
DTSIndex = Trim(Str(CInt(DTSIndex) + 1))
i = 0 'reset
Next

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources