Passing global variables to DTS using DTSRUN 1

[paoloc]

Can anyone help me understand how to access global variables passed from a DTSRUN command to the DTS?
The dtsrun statement is as follows:
dtsrun /S volcano /E /N dts_name /A foo:3=9999999

The DTS "dts_name" runs the following query:

SELECT * FROM table_name
where var1 is null and var2= ????global_variable_here????

Tks for any input.

Bst Rgds

_PC

 

[JayKusch]

First off ... the GV needs to be created within the DTS package you are executing thru the use of the DTSrun command.

the /A parameter is what tells the DTSrun util that this is a GV. You can have multiple /A parameters which would represent each GV you are passing to the DTS package.

First for good measure, create an SP for your code. Lets call it usp_SelectIt

CREATE PROCEDURE usp_SelectIt
    @SelectValue Int
  AS
SELECT * 
FROM   table_name
WHERE  var1 IS NULL 
AND    var2 = @SelectValue 
GO

SO ... in your package you may want to create an "Execute SQL Task" task. In that task you would set the connection, most likely a SQL connection based on your query, and then enter your SP as so ...

EXEC MyDB..uspSelectIt ?

The click on the Parameters button and in the Input Parameters tab, under Parameter Mapping:Input Global Variables choose the drop down for your FOO GV. If it does not exist, you need to create it by hitting the Create Global Variables button and defining it.

Remember, it must match up in name, GVs ARE case-sensitive and the data type must match. In your case you have referenced a FOO as a type 3 which is an Integer having a value of 9999999.

Once it is created, then choose it from the drop down as described above. Also remember that you have to define all the GVs that you are passing to the SP. In this case we are only passing one but if you had more, you would need to add one ? for every value. The ? is a placeholder.

Also remember that the order also makes a difference. What you pass in must match the sequence of values the SP, or other tasks, are expecting.

That should do it. Save it and execute it. I am not sure what your next process is to capture or act upon the value retreive from you SP so I will leave it at that.

Enjoy!

Thanks

J. Kusch

 

[paoloc]

Hi Jay,
tks for the help so far.... You guessed right as far the existance of a next process... Basically the result of the query performed in the SP as described above needs to be exported into a CSV file. I tried already adding a workflow upon success of the SP to no avail. Also guessed around defining export parameters.... but I don't seem to be making any head waves.

Tks/Rgds

__PC

 

[JayKusch]

OK ... have a better idea as to what you are after.

You need to create 1 connection to your SQL Server. Then create 1 connection as a "Text File (Destination).

Then join the 2 connections w/ a "Transform Data Task" task.

Double click on the new task. On the Source tab choose "SQL Query" and paste your "EXEC MyDB..uspSelectIt ?" in there.

On the same tab, choose the Parameters button. Select, or create, the GV FOO as described above.

Execute the package.

Thanks

J. Kusch

 

[paoloc]

Thanks Jay,
that did it.

Rgds
__PC