Passing Parameters to DTS

Question

Passing Parameters to DTS

Mark_maddison

Right there with Babe

Points: 768
More actions
June 20, 2002 at 2:18 pm

#105101

I am interested in creating a DTS package that I can send in some Parameters.
Can anyone point me in the right direction.
Thanks in advance

Viewing 13 posts - 1 through 12 (of 12 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

SQL 2000? If so, you can use a dyanmic properties task inside the package to get your parameters. Otherwise, I believe you can pass them in from a command line, but I'd have to dig and find out. Brian Knight knows, hopefully he'll provide some info.

Steve Jones

steve@dkranch.net

Mark_maddison Right there with Babe Points: 768 More actions · Answer 2

Hello again,

I am using SQL2K. I am making ground in passing params. Actually I am trying to pass them from ASP. In the DTS my Source is an enormous Data Source and would like the param value passed in from ASP to help whittle down the data coming from Source.

Would this be done via the sql select statement in the source ?

Thanks in advance again

Cheers

Mark_maddison Right there with Babe Points: 768 More actions · Answer 3

To all those interested in Passing Params from ASP to execute DTS package look at this link.

http://www.asp101.com/articles/carvin/dts/default.asp

Excellent Article as primer. Do the Following.

1) Create DTS package, add global var

2) Create ASP page, instanciating DTS package.

3) Create Global Var ie objPkg.GlobalVariables.Item("iAge")=50

4) You may now use these vars in DTS Active X Script with Object Browser.

5) Real power with these params was to cut down on the SOURCE connection records by making a SQL query ie Select * from Authors where Age= ?. Hit the button "Parameters" and it will associate that specific ? with a given global var ie iAge.

6) Permissions can be a lil tricky I used SQL auth.

We had ASP making up to looping to make 25000 inserts due to a custom ODBC driver. using the new method of DTS execution, the time went from about 5 minutes to about 1 minute.

Edited by - Mark_maddison on 06/26/2002 12:34:16 PM

coders4hire SSC Veteran Points: 201 More actions · Answer 4

Curious how you did the following:

4) You may now use these vars in DTS Active X Script with Object Browser.

Do you have any examples?

Doug

doug@coders4hire.com

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 5

Alternatively you can assign global variables of a DTS package via the dtsrun command line utility.

Regards,

Andy Jones

.

scotttr SSC Eights! Points: 965 More actions · Answer 6

Hey Andy,

What is the syntax for assigning global variables using dtsrun?

coders4hire SSC Veteran Points: 201 More actions · Answer 7

There does not appear to be a solution to run DTS with parameters directly from ASP. I believe the solution we're looking at is to make a 'wrapper' using visual basic... then executing it through the VB wrapper (dll).

philcart SSC-Forever Points: 47796 More actions · Answer 8

quote:
There does not appear to be a solution to run DTS with parameters directly from ASP. I believe the solution we're looking at is to make a 'wrapper' using visual basic... then executing it through the VB wrapper (dll).

Check the following post. Much neater than playing with DLL's

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=8325&FORUM_ID=19&CAT_ID=3&Topic_Title=Execute+DTS+with+Parameters+from+Stored+Procedure&Forum_Title=Data+Transformation+Services+%28DTS%29

Thanks

Phill Carter

Edited by - phillcart on 11/24/2002 5:02:24 PM

--------------------
Colt 45 - the original point and click interface

jcool SSC Eights! Points: 863 More actions · Answer 9

quote:
Hey Andy,
What is the syntax for assigning global variables using dtsrun?

Check out dtsrunui. No more building parameters or jobs for dtsrun by hand. Excellent tool!

philcart SSC-Forever Points: 47796 More actions · Answer 10

quote:
Check out dtsrunui. No more building parameters or jobs for dtsrun by hand. Excellent tool!

Pity it doesn't automagically pickup the global variables

Check DTSBrowse, it's much nicer.

http://www.databasejournal.com/features/mssql/article.php/1462591

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

jcool SSC Eights! Points: 863 More actions · Answer 11

quote:
quote:
Check out dtsrunui. No more building parameters or jobs for dtsrun by hand. Excellent tool!
Pity it doesn't automagically pickup the global variables
Check DTSBrowse, it's much nicer.
http://www.databasejournal.com/features/mssql/article.php/1462591
Thanks
Phill Carter

Look again under Advanced...

You can set Global Variables, generate command line or even generate encrypted command line

philcart SSC-Forever Points: 47796 More actions · Answer 12

quote:
Look again under Advanced...
You can set Global Variables, generate command line or even generate encrypted command line

Yes you can set the global variables, but it doesn't list the global variables that are defined in the package. You have to type them in one at a time, ie: variable name and value. The DTSBrowse tool returns the global variables and you just have to type in the values.

We have about 40 packages that have about a dozen global variables that need to be entered if you run the package by itself. Luckily it's something that is rarely done.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface