Calling a stored procedure that inserts records and generates an output parameter RRS feed

  • Question

  • I will be calling a stored procedure in SQL Server from SSIS.  The stored procedure inserts records in a table by accepting input parameters.  In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.

    How can I call this stored procedure in SSIS?  This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.

    Wednesday, April 12, 2006 8:46 AM

All replies

  • The Execute SQL Task sounds best suited to this scenario:



    Wednesday, April 12, 2006 9:34 AM
  • I've already tried that but I need to pass multiple records in the Execute SQL task, not just one.
    Thursday, April 13, 2006 4:19 AM
  • So you're going to be executing the SP for every record in the pipeline? If that is the case then the OLE DB Command transform will be of help but I would recommend you find an alternate means of doing this. Executing a seperate query for each row is not considered great practice.



    Thursday, April 13, 2006 8:47 AM
  • How do you use the OLEDB Command Transform?  Actually, I just created a simple app to do the trick.  The thing is, I cannot do anything about it.  I just need to migrate existing records into a new database.  I need to use the existing SP to insert the records from an Oracle database into the new SQL Server database because they are of different schemas and design.
    Saturday, April 15, 2006 11:38 AM