How do you call a Stored Procedure in SSIS?

Question

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?

Solution 1

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).

Solution 2

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.

Comments

[John B]

Why does such a simple task seem so complex with MS' Data Transfer Utilities??? This was easy to do in DTS, granted it took time to setup useless mappings.

[John B]

So how do I pass the data into the execute SQL task? I don't see any useful parameters in the parameter section. Only "System::xxxxx" parameters.

[John B]

That example doesn't help me, as its only 2 specific scenarios. Specifically, I am trying to run a select statement, and pass each row into a stored procedure in another server. Do you know how to do this?

[John B]

I think this works, but I'm getting a Unicode conversion error... and I'm not really sure how to fix it.

[Ed Harper]

Difficult to be certain without a bit more detail. Does your SP take char or varchar parameters?

[Ed Harper]

...that should be char and/or varchar parameters

[John B]

nvarchar. I've tried it when the target database is varchar and nvarchar. Neither work.

[D3vtr0n]

^ It does work. You need to define your OWN variables, not use the SYSTEM level variables. You should be using an "Execute SQL Task" in the Control Flow section of your package. Bring up the Variables editor and in the top left corner, add your own USER level variables.