Export procedure results to excel dynamically

[scabral79]

Hi,

I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.

The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.

Can this be done using DTS or do i have to try another method?

thanks
scott

 

[piti]

you can have a sp/query with loop to run the dts x times by calling dtsrun using xp_cmdshell sp
just create a global variable in your dts package and use a switch to pass the changing values to your package

 

[scabral79]

thanks piti,

i understand the concept of using the xp_cmdshell to call the dts package, but how do i pass a value to the dts package (bank nunmber) and store it in a global variable?

does the xp_cmdshell allow you to pass parameters?

thanks
scott

 

[piti]

you do not pass it with the xp_cmdshell options but using the /A switch of dtsrun executable