1

My current workflow is as follows:

  1. A 'Execute SQL Task' task runs a stored procedure that populates a table.

  2. After this, a 'Dataflow Task' task does the following:

    2A. Read every row from this table via a 'Dataflow Component' that uses OpenRowset.
    2B. Passes this to another Dataflow Component that does some conversions.
    2C. Writes the results to CSV.

The movement from Step 1 to Step 2A seems redundant, since the table on the server is never used again. I would like to make the stored procedure return the rows that it would write to the table and have the flow use those instead of reading the table. Is this possible?

2
  • Can the stored proc not just write records to a temporary table and then SELECT from that temp table? (I'm assuming there's logic within the stored proc that you'd have to try and replicate if you wanted to do everything directly in SSIS?). The temp table will be destroyed once the stored proc ends and the connection from SSIS gets closed
    – Craig
    Dec 21, 2023 at 23:29
  • @Craig I can easily have the stored proc return the desired values. My challenge is that I don't know how to make SSIS consume the return values of the stored proc.
    – J. Mini
    Dec 21, 2023 at 23:37

1 Answer 1

1

Actually, I may have massively over-complicated the solution below. Can you not just set the data source in your data flow to Data access mode = SQL command and give EXEC youProcedure in SQL command text? If you need to provide non-static parameters to the procedure use Data access mode = SQL command from variable and construct the SQL string in your control flow (using an expression task) before calling the data flow?


(this is assuming an OleDB connection manager, there might be slight changes needed for other sources)

  • Create a package scoped variable of type Object.
  • Configure your Execute SQL Task with ResultSet = Full result set.
  • In the Result Set tab of the SQL Task config map Result Name = 0 to the variable created above.

After this step is run the variable will contain the full resultset.

In your subsequent Data Flow Task:

  • Add a script task that is a Source, to replace your source that reads from the intermediate table in the DB. You'll need to define all the expected output columns in the Inputs/Outputs tab. Give it your Object-type variable as a read-only variable.
  • In the script code, add using System.Data; and using System.Data.OleDb; in your namespaces.
  • In the CreateNewOutputRows() method, copy the resultset to a DataTable like so:
        DataTable sprocResult = new DataTable();
        OleDbDataAdapter populateTable = new OleDbDataAdapter();
        populateTable.Fill(sprocResult, Variables.SQLTaskOutputVariable);
  • then you can iterate through with foreach (DataRow r in sprocResult.Rows), add a row to the output flow in each iteration of the loop with OutputRowsBuffer.AddRow() (change the name to match the output you defined above) and set values with OutputRowsBuffer.<columnname> = r["<columnname>"]; or OutputRowsBuffer.<columnname>_IsNull = true;
  • After that loop, call RejectedRowsBuffer.SetEndOfRowset();.

There are a few variations on this technique, mostly around using different methods to interact with the ResultSet in the C# code.

This will be inefficient for large datasets (both in terms of memory used and, to a lesser extent, processing time) because you are reading a static result into SSIS's memory space in the object variable, then copying it into the DataTable object in C#. You could instead have the source script interact directly with the DB (pass it the connection manager and have it connect and run the procedure) – this way the data will stream from the stored procedure into your dataflow without needing to be stored entirely in the object in RAM between.

5
  • So, in summary, "Yes it can, but not natively; You have to make it talk to .NET"?
    – J. Mini
    Dec 21, 2023 at 15:33
  • @J.Mini - Actually, I may have massively over-complicated that. Can you not just set the data source in your data flow to Data access mode = SQL command and give EXEC youProcedure in SQL command text? If you need to provide non-static parameters to the procedure use Data access mode = SQL command from variable and construct the SQL string in your control flow (using an expression task) before calling the data flow? Dec 21, 2023 at 16:38
  • You could be right. I'll try it in the next couple of weeks.
    – J. Mini
    Dec 21, 2023 at 18:27
  • Got it working. I thank you, but it was dramatically more complicated than expected. SQL command from variable was needed, but setting the variable required the Expression Task to set the variable in the expression itself. Using the Task's ExecValueVariable property always threw an error complaining about a type mismatch (I wanted String, but it was Empty at run time). [1 of 2]
    – J. Mini
    Jan 8 at 20:08
  • Furthermore, said variable had to be set to a valid EXEC [...] WITH RESULT SETS value before run time or else SSIS would complain endlessly about metadata mismatching even when non-run-time metadata checked were turned off. Getting this metadata correct (it had to be set in WITH RESULT SETS, the list of output columns, and the list of error columns) was agonising. On top of this, the run-time return values of the stored procedure had to agree with the metadata. This could not be checked at compile time, even though SSIS is happy to preview the query. [2 of 2]
    – J. Mini
    Jan 8 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.