asked on

DTS Package parameter passing

I have been working on a small project to design and use SQL Server DTS packages from the Enterprise Manager DTS Designer and one of my task is to call a DTS packages from another DTS package. The problem arose with passing parameters (global variables) between them.

The mechanism that I thought will implement this is using the EXECUTE PACKAGE TASK, and it has a mapping for Inner Package Global Variables and Outer Package Global Variables. But for some reason, the inner package CANNOT pick up the value I'm passing or mapping to it from the Outer Package Global Variables.

Would you know how I can pass this parameters from the outer package to the inner package (using the Enterprise Manager DTS Designer screen) and without storing the parameters to an external file or table?

ASKER CERTIFIED SOLUTION

bibana2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pespiritu

ASKER

Biban2:
This solution worked. I used something like:

Function Main()
' OPEN the target package
Set l_TargetPackage = CreateObject("DTS.Package")
l_TargetPackage.LoadFromSQLServer "MDROASVRHOMER", "****", "****", DTSSQLStgFlag,"","","","Dataprep Domain DTS"

' SET the global variable parameters using the values of the current package
l_TargetPackage.GlobalVariables.Item("@srce_context") = DTSGlobalVariables("@srce_context")
l_TargetPackage.GlobalVariables.Item("@dest_context") = DTSGlobalVariables("@dest_context")

' EXECUTE the target package
l_TargetPackage.Execute

' CLOSE the target package
l_TargetPackage.Uninitialize()
Set l_TargetPackage = Nothing

Main = DTSTaskExecResult_Success
End Function

It would have been nice if I could do this from the DTS Designer screen without writing any VBS code, but this will get me to the next step for now. Thanks.

bibana2

Glad it helped.