April 6, 2011 at 6:26 pm
I have an Store proc that returns data, actually i am converting dts to ssis. In dts they wrote ISSQL utility and made sure that, the store proc writes results set to text file .
I am wondering how can this be done ssis 2008 ... can anyone help me
April 7, 2011 at 9:36 am
Quite easily, actually.
Create a SSIS package. Add a data flow task.
Inside the data flow task, add a OLE DB source, utilizing a connection manager that points to the database where the stored proc lives. Make the Data Access Mode a SQL command, and the SQL Command Text be your stored proc name. (I found that prefixing it with EXEC is optional.) You can add parameters if the SP requires them. Verify that the columns shown as output are what you are trying to capture.
Next, add a Flat File destination, utilizing a Flat File connection manager that points to the text file you want to produce. Tie the output of the OLE DB Source to the input of the Flat File destination, match up the columns.
That's about it. One thing to remember is to include SET NOCOUNT ON at the start of your SP or SSIS gets confused when trying to determine if there is actually output from your stored proc. SET NOCOUNT OFF again at the end.
Rob Schripsema
Propack, Inc.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply