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!

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  May 20, 2010 at 3:08 am

  #1169951

  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

  May 20, 2010 at 9:43 am

  #1170241

  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

  May 20, 2010 at 7:20 pm

  #1170547

  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