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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

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?  

0
pespiritu
Asked:
pespiritu
  • 2
1 Solution
 
bibana2Commented:
One thing you could try doing is using an Active X script to set the global variables into the DTS package you want to call.

EX:

Set oPackage = CreateObject("DTS.Package")
oPackage.LoadFromSQLServer "SERVER NAME", "SERVER USERNAME", "SERVER PASSWORD", DTSSQLStgFlag,"","","","NAME OF PACKAGE"
oPackage.GlobalVariables.Item("GLOBAL_VAR_1") = Some_Data




Then you call the package from within the Active X script.  


oPackage.Execute
oPackage.Uninitialize()
Set oPackage = Nothing




I'm sure there are other ways of doing it, but this way works for me.  Hope it helps.
0
 
pespirituAuthor Commented:
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.

0
 
bibana2Commented:
Glad it helped.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now