How do I pass parameters into a DTS package?

Question

How do I pass parameters into a DTS package?

Jeremy Kemp

SSCertifiable

Points: 6754
More actions
April 14, 2004 at 5:59 am

#60072
I have a horizontally partitioned database with a different table for each year (for 1997 onwards).
I have a DTS package which produces a control report for a specific year (2004) as follows:
- get the control report file (imported into a temporary table)
- get the summary numbers from the table
- compare the two sets of numbers
- email results
The only thing that differs from one year to another year is the name of the control report file (it has a year identifier in the name of the file) and the name of the table in step 2.

Rather than have lots of near identical DTS packages, how can set up 'global parameters' so that I can pass these in from an SQL job - there being one job for each different year?

Thanks
Jeremy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

When you call the DTS add a switch /A and create global variables in the DTS for each that you need to pass in, for example...

In the DTS you want to pass the control filename so create a global variable of String type called CTRLFILE

In the job, when you call the DTS package (example DTS called Create New File)

DTSRUN /SMYSQLSERVER /N"Create New File" /E /A CTRLFILE:8=c:\yourfile.txt

The 8 after CTRLFILE signifies the datatype of the parameter you are passing in, for a list of the types check out DTSRUN in BOL.

Shamless self promotion - read my blog http://sirsql.net

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

Thanks and a couple of questions.

1. What's the best way of checking whether the DTS package is using the passed parameters? I read in BOL that, depending on ownership and authorities, the default values in the package are used rather than the values passed. I have tried executing DTS with a userid with SA priviliges but when I open the package and check the global variables the value has not changed.

2. How do you pass multiple parameters? Is is /A parm1:8=value /A parm2:8=value or what?

Thanks

Jeremy

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

When you open the package the variables will not have changed, they are simply altered at run time, because of this they will always reflect the values that were set when the package was last saved. You could export the variables to a text file as a part of the package to prove their run time values.
Multiple parameters would be passed exactly as you had there.

Shamless self promotion - read my blog http://sirsql.net

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 4

I proved to myself with a msgBox statement that it is picking up the parameters at run time.

Thanks

Jeremy