Post reply

Data Import from CSV file

  • November 27, 2012 at 10:32 am

    #1562766

    Phil Parkin (11/27/2012)

    There is no good way of doing this in SSIS - I'm with Jeff on this one.

    If you want to put an SSIS 'wrapper' round it, something like this would work:

    1) Exec SQL: Truncate Staging table

    2) Dataflow: Insert to Staging table

    3) Exec SQL: UPSERT logic to move from Staging to main data table

    Thanks Phil.

    However I went through some posts where they have suggested look up transformation for this.

    But not sure if it would not hit the performance in future.

  • November 27, 2012 at 10:51 am

    #1562771

    rhythm.varshney (11/27/2012)

    Phil Parkin (11/27/2012)

    There is no good way of doing this in SSIS - I'm with Jeff on this one.

    If you want to put an SSIS 'wrapper' round it, something like this would work:

    1) Exec SQL: Truncate Staging table

    2) Dataflow: Insert to Staging table

    3) Exec SQL: UPSERT logic to move from Staging to main data table

    Thanks Phil.

    However I went through some posts where they have suggested look up transformation for this.

    But not sure if it would not hit the performance in future.

    The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 27, 2012 at 3:08 pm

    #1562867

    rhythm.varshney (11/26/2012)

    Jeff Moden (11/26/2012)

    rhythm.varshney (11/26/2012)

    Phil Parkin (11/26/2012)

    rhythm.varshney (11/26/2012)

    waiting for your valuable input guys........

    You still have not asked a single question - there is nothing to respond to.

    please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.

    Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

    "Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

    Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.

    Thanks Jeff.

    Actually I did the same and suggested the same even wrote all UPSERT logic but my lead wants it to be implemented using ETL. Don't know why though I asked him that handling it in DB would be more easy to maintaing considering future changes and performance(as I read somewhere tha SSIS is not so good in update) also.

    So could you please suggest the approach to get it done via SSIS.

    Just to be clear, "ETL" doesn't mean "SSIS", which can, of course, do "ETL". To wit, SSIS frequently requires a T_SQL script to do some "ETL". I just cutout the middleman and avoid SSIS so I'm of no help there.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 29, 2012 at 3:40 pm

    #1564081

    Phil Parkin (11/27/2012)

    rhythm.varshney (11/27/2012)

    Phil Parkin (11/27/2012)

    There is no good way of doing this in SSIS - I'm with Jeff on this one.

    If you want to put an SSIS 'wrapper' round it, something like this would work:

    1) Exec SQL: Truncate Staging table

    2) Dataflow: Insert to Staging table

    3) Exec SQL: UPSERT logic to move from Staging to main data table

    Thanks Phil.

    However I went through some posts where they have suggested look up transformation for this.

    But not sure if it would not hit the performance in future.

    The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.

    Hi Phil,

    Could you please help me out to deal with duplicates using look up as you mentioned.

  • December 3, 2012 at 5:37 am

    #1564809

    I got it by following way.Please let me know if there is any better way of doing it via SSIS transformations.

    1.Truncating and filling records from CSV to staging table.

    2.Putting look up on staging table and filling records in base table.(matched rows go for update and unmatched rows for insert)

    3.To deal with duplicates I put an Execute SQL Task in control flow and there I have built query to remove duplicates rather updating the same record if it is there as per my requirement.

  • December 3, 2012 at 6:46 pm

    #1565106

    Phil Parkin (11/27/2012)

    rhythm.varshney (11/27/2012)

    Phil Parkin (11/27/2012)

    There is no good way of doing this in SSIS - I'm with Jeff on this one.

    If you want to put an SSIS 'wrapper' round it, something like this would work:

    1) Exec SQL: Truncate Staging table

    2) Dataflow: Insert to Staging table

    3) Exec SQL: UPSERT logic to move from Staging to main data table

    Thanks Phil.

    However I went through some posts where they have suggested look up transformation for this.

    But not sure if it would not hit the performance in future.

    The cached and partially cached lookups won't work because you have duplicates (by name) in your source data. Maybe the uncached lookup would work, but it is performance suicide when compared with what the SQL Server engine can give you.

    I would like to know how we can handle the duplicates via Look-up.

    Could you please share any link or give me idea to achieve it by Look up transformation keeping performance point aside just for my learning.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply