how to reuse the different set of stored procedure code for single ssis package to two different output flat file destinations.

Question

Hi Friends,

how to reuse the different set of stored procedure code for single ssis package to two different output flat file destination.

If run the below query  in sql server output gives two results.

I created procedure like below,

USE [AdventureWorks]
GO
create procedure [dbo].[GetManagersAndEmployees]
 @employeeId int
as
begin

EXECUTE [AdventureWorks].[dbo].[uspGetEmployeeManagers]
   @EmployeeID
EXECUTE [AdventureWorks].[dbo].[uspGetManagerEmployees]
   @EmployeeID

end
GO

exec [GetManagersAndEmployees] 90

Thanks

Lakshmi

Answer 1

Hello,

Check out the link below. It might help you.

http://stackoverflow.com/questions/7095930/ssis-script-task-output-data-to-files-from-multiple-stored-procedure

Answer 2

This article describes how to handle multiple result sets in a script component:

http://www.codeproject.com/Articles/32151/How-to-Use-a-Multi-Result-Set-Stored-Procedure-in

Although I recommend not doing it this way. Why not execute the two stored procedures seperately and save yourself a lot of headaches?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 3

Hi Lakshmi,

Create two object variables in ssis

and use script task, copy the below code.

SqlConnection conn = null;
try
{
 // create and open a connection object
           conn = new SqlConnection("Server=.;DataBase=master;Integrated Security=SSPI");
           conn.Open();
           SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter("sp_anil", conn);
           adap.SelectCommand.CommandType = CommandType.StoredProcedure;
           DataSet DS = new DataSet();
           adap.Fill(DS, "Table1");
           DataTable combinedTable = new DataTable(); //Create columns
           DataTable dt1 = DS.Tables[0];
           DataTable dt2 = DS.Tables[1];
           foreach (DataRow dr in dt1.Rows) 
                {
                    MessageBox.Show(dr[1].To String());
                
                }
                object abc = dt1;
                object asd = dt2;
            }
            catch
            { }
             // Assign the temp object variable values to the ssis variables.

Now use Ado.net enumarator to acces the rows in that variables. 

Answer 4

you can check this link :http://msftisprodsamples.codeplex.com/wikipage?title=SS2008!ExecuteSQLParametersResultSets%20Sample&ProjectName=msftisprodsamples

another approach will be using script task as mentioned above.

regards

joon