How do you call a Stored Procedure in SSIS?

sql-server sql-server-2005 stored-procedures ssis parameters
John Bubriski picture John Bubriski · Apr 13, 2009 · Viewed 95.9k times · Source

I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row.

In my old DTS package, I was doing this:

EXEC myStoredProcedure ?, ?, ?

...and then I mapped the parameters. However, in SSIS, I can't figure out how to make this work.

I have a Data Flow task, which first runs a query for the data. It passes the data to an OLE DB Destination. I set the Data access mode to "SQL command", but when I try to put in the SQL above, I get "Invalid Parameter Count" when it parses the SQL. I can't get to the Mappings screen. Any ideas?

Answer

Ed Harper picture Ed Harper · Apr 14, 2009

In the Data Flow, the OLE DB Command can be used to execute a SQL statement for each row in a dataflow - (MSDN documentation)

Alternatively, you can store the source result set in a variable of data type object and use a Foreach Loop container in the Control Flow (example here).

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?

Read More