Passing parameters to Transform Data Task

Question

Passing parameters to Transform Data Task

gujsa01

SSC-Addicted

Points: 408
More actions
April 13, 2005 at 3:23 pm

#76280

Hi ,
I have a DTS Transform Task , "ABCExport", that outputs the result of a Stored Procedure to a Flat file. This Stored Procedure accepts parameters like:
exec PrcName '076','SHORESME', '03/01/2005', '03/31/2005'.
What changes every month are the 2 dates.
This DTS Transform Task is called by a Stored Procedure as follows:
exec master..xp_cmdshell 'dtsrun /S TestLab /N ABCExport /U TestLogin /P password'
I would like to parameterize the above statement which in turn gets passed on to the exec PrcName .. statement mentioned above.
Appreciate any help on this and thanks in advance.

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

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

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 1

I used scheduled jobs to kick off DTS but they also use DTSRun. I create a dynamic properties task within the DTS that allows the Server, Database, and several other parameters to be passed into the DTS at run time as "Global Variables". The dynamic properties task relates the global variables to specific properties of the other tasks.

DTSRun allows you to specify global variables on the command line with the /A switch. Because I use .BAT files, I use DOS substitution variables, but you would use stored procedure parameters and string concatenation.

DTSRun /S %SERVER% /E /N %REPORTNAME% /A File:8=%REPORTFILE% /A Server:8=%SERVER% /A Catalog:8=%CATALOG% /A Exceptions:8=%ERRORFILE%

'DTSRun /S '+@server+ ' /E /N '+@dtsPackage+ ...'

Be careful though and try to detect injection attacks, that are similar to SQL injection attacks. You are essentially allowing a dynamic DOS statement that might include a command separator ('&')with additional statements appended. xp_cmdshell allows you run more than one command.

augustin Carnu-215787 SSC Enthusiast Points: 192 More actions · Answer 2

If I understand you correctly, you want to pass the 2 dates as global variables: use /A option on the command line, one for each date. I will try it first with a dummy stored proc and log execution for DTS all the way..

Cheers,

Augustin Carnu

gujsa01 SSC-Addicted Points: 408 More actions · Answer 3

Appreciate the response guys.

I will try this out and keep you posted with any update(s).