Using Stored A Procedure with Temp Tables for SSIS Packages

Using stored procedure with temp tables for SSIS packages.

Some stored procedures are complex and so require temporary tables or table variables.

Let us say we have the following procedure to extract data.

CREATE PROC ProcWithTableVariable
AS
DECLARE @Table TABLE (id Int)
INSERT INTO @Table
VALUES (1),(2)
SELECT * FROM @Table

And SSIS OLE DB Source editor is configured as shown below:

Using this you can preview data without any issues.

However, your SSIS package execution will fail with the following error.

This can be fixed by introducing SET NOCOUNT ON to the procedure as shown below.

CREATE PROC ProcWithTableVariable
AS
SET NOCOUNT ON
DECLARE @Table TABLE (id Int)
INSERT INTO @Table
VALUES (1),(2)
SELECT * FROM @Table

]]>