none
Transferring data from one table to another table using a Keycolumn using SSIS row by row dynamically

    Question

  • Hi All,

    I have a Store Procedure(SP) which has a output variable name "AcivityID" which is the key column. In this SP, transformation  of data is done from one table to insert data into other table. I have to execute the SP and insert row by row data using the output variable "ActivityID"  whose value will keep on changing. How can I do it?

    Thanks,
    Kallu

    Tuesday, July 08, 2014 6:10 PM

Answers

  • You can capture the value in SSIS by using a placeholder and mapping a SSIS variable in parameters tab of execute sql task. you just need to set parameter type as Output.

    But I would second last suggestion. You really dont need to do this row by row in SSIS. You can simply use OUTPUT clause to capture all generated values in set based way to a table. Then you can use it in your query by joining to other tables.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 5:11 AM

All replies

  • Value changing on a row by row basis? Not quite sure what you mean, but it seems that you want to use the results of an insert into one table as input for another. If so then SSIS is not needed, inside the stored proc use the SQL that will do that and for all records as

    INSERT A INTO dbo.table1
    OUTPUT INSERTED.A INTO MyTable;


    Arthur My Blog

    Wednesday, July 09, 2014 3:09 AM
  • You can capture the value in SSIS by using a placeholder and mapping a SSIS variable in parameters tab of execute sql task. you just need to set parameter type as Output.

    But I would second last suggestion. You really dont need to do this row by row in SSIS. You can simply use OUTPUT clause to capture all generated values in set based way to a table. Then you can use it in your query by joining to other tables.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 5:11 AM