We help IT Professionals succeed at work.

How to Pass in DTS parameters

suenram asked
There are 3 parameters coming to a Stored Procedure which kicks off a DTS Job.
@Filename              For example-- 'Task Summary'

Kicksoff a DTS Task (DTS_Load_Task_Summary)
This Loads the file 'Task Summary' into a Temp table. (not #/##)

Job Step: (sp_createTmpTaskSummary)
Stored Procedure has been created that creates a permanent Temp table (and this works okay)
'Create Table
  '+ 'tmp' +''+ @UserNm +''+ @SavedDateLast +''+ 'Task_Summary' +'

For the DTS Filepath, I want this to be dynamic
next time \\yyyyyy\yy$

DTS Questions:
1) I need a sample of how to get the (variable) Filepath in to the DTS Connection Property
2) I need a sample of how to get the @usernm into the SP (sp_createTmpTaskSummary)
The date is created with getdate() do don't need help with the Date.

3) Related question is where (connection properties?) in the DTS job should I establish
the UNC which says the network
drive where the file is located?  I believe I have the permission issues settled.

I have not used parameters in DTS so I am confused.  There are parameters behind the SQL
statement and there are parameters for DTS and I have not used these before. I don't know
how they work or how to pass something in.  Help please.
Watch Question

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
If you have SQL 2000, then DTSRUN supports to pass global parameter values with the /A flag (see books online for dtsrun command line utility

Otherwise, i use the following "workaround":
* define the DTS to load from a fixed file, and load to a fixed table
* in your stored procedure, move the actual file to the place where the DTS expects it
* after the dts, move the rows from the temp table to the actual temp table you would expect it
(if you want to do this in the DTS, you could save the settings in a table, read those settings in a VBScript task, and move the file as needed etc)

Hope this helps




I would like an example of what the @UserNm for a Stored Procedure would look like in this command if passed as a variable.  
DTSRUN /S "(local)" /E /N csi_Load_Task_TP /A ....

And I would like an example of what this Path would look like in the same command if passed as a variable.

DTSRUN /S "(local)" /E /N csi_Load_Task_TP /A ....

Are these variables all set in the same place,
DTS Package Properties, Global Variables?



For now, just trying to get variables into the stored procedure.  The command below generated by the Utility does not work.
I am not sure when doing the global variables --Name, Type, Value, should you see @Usernm @SavedDate?  I am not sure if @ is relevant anywhere outside of the Stored Procedure.  Muck.txt just says --The task reported failure on execution.
Step Error code: 8004043B

DTSRun /S "RNAsuenramk\suenramk" /N "CSI_Load_Task_TP2" /G "{F35D7502-47B1-4331-8557-EAC2D940042B}" /L "D:\Suenram\Word\Muck.txt" /A "UserNm":"8"="'suenramk'" /A "SavedDate":"8"="'20020120'" /W "0" /E


Procedure 'csi_CreateTmpTaskTP' expects parameter '@UserNm', which was not supplied.)
Step Error code: 8004043B

New Message:

Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80040e14): Must declare the variable '@UserNm'.)