Grab a Return Value from a SP

[u104741]

Hi i am very new to DTS but have a little experience of Stored Procs... so sorry if i am asking a silly question.

Esentially i need to execute a stored procedure from within a DTS pacakge the Stored Procedure will return a Table_Name.

Within my DTS Package I want to store this Table_Name as a Global Varaible so I can use it in a SQL Statement. I will then export the results of the sql as a csv file.

Now I am ok getting DTS ro run a SQL statement and export the results to a csv file.. + running a SP is ok, but how do I assign the returned value from a SP to a global variable and then use that global variable in my sql statement in the FROM clause.

Any help will be grately received i know this is possible!

 

[JayKusch]

OK ... here is how we start.

Right-click on the desktop. Choose Package Properties.

Then go to the Global Variables tab.

Enter the GV Name/Type and Default value. click OK

Now choose a Dynamics Property Task. and then open the task.

Now click ADD.

Click on the GV and then highlight you GV. Then click the Property Name of the GV.

Click on the Set... button and change your source to a Query. Paste your code in and set your connection.






Thanks

J. Kusch

 

[u104741]

J Kusch. .you diamond !!! thanks. I think i am getting there now.

 

[u104741]

made sum progress but stumped once again...

I now have my Global Varaible but how to i use it to generate a sql statement.

I have a 'Transform Data Task' which is associated to a DB ection and a Text File destination. Currenty it has a fixed SQL select statement which = 'Select * from Buildings' i want to set this select statement dynamically usuing my new Global Varaible ie. 'Select * from Table_Name'.

I am thinking that once the g;lobal variable is set i need to run a VB script that changes the SQL statement on the 'Transform Data Task' but cant woprkl out how to do this.

If you could help me some more I would very much appreciate it.