Hey there, let's work through this SSIS challenge together—you've already got the stored procedure results in a ResultSet variable, so we just need to bridge that to an Excel file. Here are two reliable approaches tailored to your scenario:

方法1：利用已有的ResultSet对象变量，通过数据流读取后导出 #

If you already have the stored procedure's results saved to an object-type variable (e.g., @User::obj_StoredProcResults) via your Execute SQL Task, follow these steps:

1. Add a Foreach Loop Container to your Control Flow, then place a new Data Flow Task inside it.
2. Configure the Foreach Loop Editor:
   • Select Foreach ADO Enumerator as the enumerator type
   • Pick your ResultSet object variable from the "ADO object source variable" dropdown
   • Choose "Rows in first table" (since your stored procedure returns a single table)
3. Inside the Data Flow Task:
   • Add a Script Component and set it as a Source
   • In the Script Editor's "ReadOnlyVariables" section, select your ResultSet object variable
   • Write a quick script to read the DataTable from the variable and output rows to the data flow. Example C# code:

     using System.Data;
     using Microsoft.SqlServer.Dts.Runtime;
     
     public override void CreateNewOutputRows()
     {
         DataTable resultsTable = (DataTable)Variables.obj_StoredProcResults;
         foreach (DataRow row in resultsTable.Rows)
         {
             Output0Buffer.AddRow();
             // Map each column from your result set to the script output columns
             Output0Buffer.CustomerID = Convert.ToInt32(row["CustomerID"]);
             Output0Buffer.CustomerName = row["CustomerName"].ToString();
             // Repeat for all columns in your stored procedure's output
         }
     }
     

   • Add an Excel Destination component, connect it to the Script Component, and map all output columns to your Excel file's columns.

方法2：直接在数据流中调用带参数的存储过程（跳过预存ResultSet） #

If you'd prefer to cut out the intermediate variable step, you can call the parameterized stored procedure directly in the Data Flow:

1. Add an ADO.NET Source to your Data Flow, and connect it to your SQL Server connection manager.
2. In the ADO.NET Source Editor, select "SQL command" and enter your stored procedure call with parameter placeholders:

   EXEC dbo.YourTargetStoredProcedure @InputParam1 = ?, @InputParam2 = ?
   

3. Click the Parameters button, then map your SSIS input variables (the two you mentioned) to the stored procedure's parameters. For example:
   • Left side (parameter name): @InputParam1 → Right side (SSIS variable): @User::var_Input1
   • Repeat for the second input parameter
4. Add an Excel Destination component, connect it to the ADO.NET Source, map the columns, and you're ready to run the flow.

Key Tips to Avoid Headaches #

• Double-check that your Excel connection manager uses the correct driver (e.g., ACE OLEDB 12.0 for Excel 2016+) and that the file path is valid.
• Ensure the data types of your stored procedure's output columns match the Excel destination columns (e.g., SQL nvarchar → Excel text, SQL int → Excel number).
• If using Method 1, confirm your Execute SQL Task is set to return a "Full result set" and mapped to an object variable of type System.Data.DataTable.

内容的提问来源于stack exchange，提问作者Arpit Chinmay