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



Related Articles :

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |