May 5, 2005 at 10:31 am
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
May 5, 2005 at 5:16 pm
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
May 5, 2005 at 10:05 pm
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
May 5, 2005 at 10:34 pm
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
May 6, 2005 at 6:55 am
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.
May 6, 2005 at 7:46 pm
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
next
'destroy our package object
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
May 6, 2005 at 7:49 pm
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
May 9, 2005 at 12:22 pm
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
next
Calling Package executes successfully but does not actually invoke P1 and the Step
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply