Executing a step in a DTS package from Another DTS package

Question

Post reply

Executing a step in a DTS package from Another DTS package

THNQdigital

SSC Eights!

Points: 849
More actions
May 5, 2005 at 10:31 am

#61864

Hi

Offlate i am using DTS too much !. I have some DTS packages and some of the steps are like common and i want to re use them instead of re creating same steps in all the packages..

Like for example i have a step to send an email , i am just wondering if i can execute this step ( which i spart of DTS_P1) from package DTS_P2. I know how to load and execute an entire package from another paclage.. but can't figure out yet how to get hold of a single step in package and execute only that being in another DTS Package...

Anythoughts on this..?

Thanks

THNQdigital

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Haven't done it myself, but you might be able to do it via some scripting. I've heard of methods that load VBScript for ActiveXScript tasks from a central package.

Probably a better method is to put the functionality you want to call from multiple packages into its own package then just execute that package.

--------------------
Colt 45 - the original point and click interface

mike gercevich SSCrazy Points: 2961 More actions · Answer 2

Loading another package into memory on many tasks within on package will surely cause performance issues. Another solution, if you are using a mess of the same routines, over and over...

Put all the functions, constant declarations and routines into a single text file that is accessable via URL for the account that is executing your packages.

Place an Active-x task that is disabled in all of your dts packages that is not a member of any workflow path. - name it CodeLibrary.

Your beginning steps workflow can load in the file and update the CodeLibrary's Active-X script with the contents of your text file. (make sure you do not have a Main() function within the text file).

Each Task that will require the use of any of the function in CodeLibrary can read the contents into a local variable (do this in the Global Declaration area of the script -- above Main ). Then Issue a VBScript Execute( myvar ).

All the functions, constants and subroutines will be available to the Main function of that script.

If you need to add additional routines aor constants, just update the text file.

I have used this method to log messages and provide error routines, etc...

Hope this helps,

-Mike Gercevich

philcart SSC-Forever Points: 47794 More actions · Answer 3

Mike

I use a central error reporting package that is called via script in all of the On Error paths. There doesn't seem to be any performance issues and I have 8-10 packages executing in parallel at times.

Loading the script into a CodeLibrary is sort of what I was alluding to in my previous post. I've been meaning to do something like this for quite a while. It would make the code that calls my error reporting package a lot easier to maintain. The main difference is that I'd have a single DTS package that acts as the script library. Then just pull the scripts out of there.

Probably won't get around to doing anything with it now as it'll all change with SQL Server Integration Services.

--------------------
Colt 45 - the original point and click interface

jablakejr Grasshopper Points: 17 More actions · Answer 4

If they are distinct job steps, you can code the common routines as stand alone DTS packages. Then when you are coding a needed multi-step package requiring one of these common routines there is an 'task' option in the DTS designer to execute packages that asks for the DTS package name (here you can reuse your stand alone routines by name).

Once in the design pane, you can use the process-flow vehicle just as you can for inline SQL or activex tasks you code. Finally, schedule the new multi-step package to run as a job as you would before.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 5

Function Main()

CallStepInPackage ".","hi","CommonActiveXScript"

Main = DTSTaskExecResult_Success

End Function

Sub CallStepInPackage(strServer,strPackage,strStep)

'dims

dim objPackage, objStep

'create package object

set objPackage = CreateObject("DTS.Package2")

'use windows authentication and load dts package

objPackage.LoadFromSQLServer strServer,,,256,,,,strPackage

'get our step

for each objStep in objPackage.Steps

if objStep.description = strStep then

msgbox "I found step " & strStep & " in package " & strPackage & " on server " & strServer & " and I'm fixing to try to execute it."

objStep.execute

end if

set objPackage = Nothing

End Sub

I created a dts package named "hi" with one activexscript task called CommonActiveXScript that contained the following script:

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

msgbox "HI!"

Main = DTSTaskExecResult_Success

End Function

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 6

Roy Ashbrook

SSChasing Mays

Points: 632

May 6, 2005 at 7:49 pm

#557479

also note that you can call the task directly if you know the name of the task. i know you can change the name of the task in the disconnected edit properties page, not sure how else via the gui. if you know the name you can call it simply like this once you load the package:

objPackage.Steps("MyName").Execute

THNQdigital SSC Eights! Points: 849 More actions · Answer 7

Hi Roy,

Thanks for your time.. i tried your code..

Inside Package P2

oPackage.LoadFromSQLServer "","","",DTSSQLStgFlag_UseTrustedConnection,"","","","P1"

for each objStep in oPackage.Steps

if objStep.Description = "Sendmails" then

objStep.Execute

end if

I tried with oPackage.Steps("Sendmails").Execute , now i get error message "Step Sendmails not found".. Sendmails is a step in P1. Any thoughts..?

Thanks

THNQdigital