question

apple avatar image
apple asked

Multiple Outputs from SP

Hello! In SSIS package I need to use a stored procedure with two outputs. Is there any way to do other than using Script Component? I am not good with .net or C# coding and I am having trouble debugging the errors with Script component. Thank you
ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
I don't know of a way to do what you want without a script component, but maybe this article by Tim Mitchell will help with doing it that way. [ http://www.timmitchell.net/post/2015/04/20/using-the-ssis-object-variable-as-a-data-flow-source/][1] Please let us know how this works out for you and if you have additional questions. I might try playing around with it over the weekend if I have time. [1]: http://www.timmitchell.net/post/2015/04/20/using-the-ssis-object-variable-as-a-data-flow-source/
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

apple avatar image apple commented ·
Thank you Tom I tried Bob Pearson's .Net code on http://www.codeproject.com/Articles/32151/How-to-Use-a-Multi-Result-Set-Stored-Procedure-inI was running into issues . I will try Tim Mitchell's code and will post if I encounter similar issues. Once again Thanks a lot
0 Likes 0 ·
apple avatar image apple commented ·
Hi! I am following the steps in the link http://www.timmitchell.net/post/2015/04/27/the-ssis-object-variable-and-multiple-result-sets/ SqlCommand cmd = new SqlCommand("EXEC [dbo].[usp_GetSalesData]", conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // Read() will return false once we've processed all rows in the first result set. { // Create a new, empty row in the output buffer SalesOutputBuffer.AddRow(); I am getting an error at SalesOutputBuffer.AddRow(); It says SalesOutputBuffer does not exist in the current context. Do I need to define it and if so where should we define? Thank you
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
SalesOutputBuffer is the name of the output buffer in Tim's example. The default name is Output0Buffer. If that doesn't work, go to the "Inputs and Outputs" page of the Script Transformation Editor and check the name of the output. The buffer name will be that name without spaces followed by "Buffer". Remember, names are case-sensitive in C#.
0 Likes 0 ·
apple avatar image apple commented ·
Thanks a lot Tom I modified the name in the inputs and outputs window and It does not show the previous error .It does not show the name buffer in the window under the properties of the output window I created next to the Name. Next task is mapping columns Thanks a lot
0 Likes 0 ·
apple avatar image apple commented ·
Thank you very much Tom for the guidance I was able to load the data into the Excel.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.