Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    17

    Question Unanswered: How could I pass a parameter to DTS package?

    I build a DTS package to get data from Oracle.
    The data I want may be yesterday, may be two days ago or may be others. So I need a date parameter to tell the package how to get the data. But I do not know how to do it.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Lots of ways to skin this cat; depends on exactly what you want to do. From your question, it was not entirely clear to me what you were trying to achieve. One way to set a parameter inside a DTS package is to use a "Set Dynamic Properties" task to set the value of a global variable. Then use the global variable inside a Data Pump (use a '?' inside the SQL Query to place a parameter and finally associate the parameter(s) to the '?' by clicking on the "parameters" button underneath the SQL Query window.

    Another way is to set a package level global variable from a separate DTS package (or from a VB Script) and then call the target DTS package. This is done sometimes when a loop mechanism is needed.

    Code:
    	Set oWatchlistPackage = CreateObject("DTS.Package2")
    	oWatchlistPackage.LoadFromSQLServer "myserver",,,256,,,,"MyDTSPackage"
    
    	oWatchlistPackage.GlobalVariables.Item("sFileName").Value = oRS("ORIG_FILE_NAME")
    	oWatchlistPackage.GlobalVariables.Item("iAgency").Value = oRS("AGENCY")
    	oWatchlistPackage.GlobalVariables.Item("sUserName").Value = oRS("MODIFIED_BY")
    	oWatchlistPackage.GlobalVariables.Item("iFileID").Value = oRS("FILE_ID")
    
    	oWatchlistPackage.Execute
    In either case, consult with www.sqldts.com and do a search using Google. You'll find quite a few other (and probably better) examples out there.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •