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



Array

No comments yet... Be the first to leave a reply!