23

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?

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
John B
  • 20,062
  • 35
  • 120
  • 170
  • 2
    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 Apr 13 '09 at 21:09
  • I'm sure you can do this at the 'data flow' level, in fact I'm sure I've done it but I can't remember how. How else are you supposed to, for instance, run an INSERT proc for failed lookups – adolf garlic Apr 14 '09 at 12:11

2 Answers2

15

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

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
8

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.

Lance Harper
  • 2,216
  • 14
  • 11
  • 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 Apr 13 '09 at 20:29
  • 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 Apr 13 '09 at 21:10
  • ^ 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. – D3vtr0n May 15 '09 at 21:01