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?
You can call an SQL stored procedure with the execute, open, or get statement; in each case, you use the #sql directive. A stored procedure is a set of instructions for a database, like a function in EGL.
The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.
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).
You will need to use an Execute SQL Task. In the SQLStatement section you can add the code to execute the stored procedure.
In order to pass in parameters, use the ? syntax and specify the parameters in the "Parameter Mapping" section.
A good example can be found here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With