Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute stored Procedure from SSIS Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 5:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 10:22 AM
Points: 64, Visits: 252
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!
Post #924754
Posted Thursday, May 20, 2010 3:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #924924
Posted Thursday, May 20, 2010 9:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 10:22 AM
Points: 64, Visits: 252
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.
Post #925276
Posted Thursday, May 20, 2010 7:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 12:03 AM
Points: 125, Visits: 408
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
Post #925622
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse