Execute stored Procedure from SSIS

Question

Post reply

Execute stored Procedure from SSIS

chaudharyabhijit

Ten Centuries

Points: 1314
More actions
May 19, 2010 at 5:41 pm

#156967

Hello All,
I have a stored procedure which i want to execute using SSIS and save the output in a flat file. I would really appreciate comments on how to do it.
thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Not 100% sure, but I thinks this is how you should do it:

1. Take an Execute SQL Task and execute your stored procedure there with the following code:

EXEC my_stored_procedure my_par1 mypar2 ... ;

GO

2. In the General Tab, set Result Set to Full Result Set (I hope this can be done with stored procedures)

3. In the Result Set tab, write the results to a variable of type object.

4. Create a data flow task and add a script component that acts as a source. Read the object variable and write it to the output buffers. Then write it to a flat file destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 2

Ok Its working now, i created a OLEDB Source and executed the stored procedure as a sql command. Then once it would populate the view it was very easy to just add a flat file destination.

vani_r14 SSCrazy Points: 2843 More actions · Answer 3

Hi there

I realise that you have worked it out, but recently i had a similar problem and had to use the Execute Sql task. This document is really helpful

http://technet.microsoft.com/en-us/library/ms140355.aspx

I used this to walk through the process and it worked miraculously

Cheers