locked
SSIS : Insert records into Parent Child table RRS feed

  • Question

  • Hello,

    I am creating SSIS package where below mentioned is the scenario :

    1. There is sourcefiles folder -- > Need to pick CSV files of yesterday's date for processing.
    2. There are many validations which needs to be done.
    3. Store all (valid + invalid) rows in SQL destination Parent table.
    4. Store invalid rows in child table with Foreign ID of parent table.

    There are around 30K to 40K rows in the CSV file.

    I have created Script component to perform validations on each row. This is running very slow. Any solution to this ?

    I have defined two outputs of this script task.

    Also,

    1. I am not getting an idea how to get Scope identity of Parent table inserted row ?
    2. Which approach I should use to insert these rows --> Data flow, Execute SQL Task or in script component ? which one would be faster in this scenario ?
    3. Can I use Bulk Insert ?

    Can anyone please help me on this ?

    Thank you, Mittal.

    Friday, May 29, 2015 7:50 AM

Answers

    1. I am not getting an idea how to get Scope identity of Parent table inserted row ? - Read this:  http://stackoverflow.com/questions/8914818/insert-a-single-row-and-return-its-primary-key
    2. Which approach I should use to insert these rows --> Data flow, Execute SQL Task or in script component ? which one would be faster in this scenario ? - You could load data into a Staging table, then use Stored Procedure to Insert Records
    3. Can I use Bulk Insert ? - Bulk insert your parent child data to two staging tables then execute a stored procedure to load data into parent child table
    4. I have created Script component to perform validations on each row. This is running very slow. Any solution to this ? - well, without looking into your script it is hard to give/advise any solution.

    Please don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. web: www.ronnierahman.com

    Friday, May 29, 2015 8:15 AM