Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grab a Return Value from a SP

Status
Not open for further replies.

u104741

MIS
Aug 26, 2003
94
GB
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!

 
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
 
J Kusch. .you diamond !!! thanks. I think i am getting there now.
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top