Post reply

CSV file import using ssis

  • November 15, 2012 at 8:52 am

    #150124

    I have a CSV file as follows:

    "W3713","20121114171956","0.00","1","Standard","33646X","571270/6/1/69","0.00",

    "W6372","20121114171956","0.00","2","Standard","33646X","571811/09/10","0.00",

    As you can see the last column has a comma expecting a field. This field is called FuelSurcharge. How can i force my SSIS to ignore this and force the import of the CSV file fields into the database table. When i run the package it throws an error. If i put extra fields at the end it imports ok.

  • November 15, 2012 at 8:59 am

    #1559415

    can't you change it so that the ending delimiter is comma-Carriage-Return instead of just CarriageReturn?

    that would fix it i think.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 15, 2012 at 9:03 am

    #1559416

    Lowell is right. If you don't need the field, then leave FuelSurcharge out of the CSV altogether. We are, of course, assuming you have control over the generation of the file. If that is not the case, then you could use a script task and some string manipulation to strip the trailing comma from the file before your data pump task.

  • November 15, 2012 at 9:24 am

    #1559429

    Thanks for your suggestions. I do need the field. Its just the field may not be included in some daily files. How would i use the script task editor to remove the comma if there is no field?

  • November 15, 2012 at 9:28 am

    #1559431

    How is the flat file being generated? Even if the data is not included, you should still be getting a blank string for the field so the comma would just be followed by double-quotes.

    One of the keys to ETL is the source file format needs to be consistent. Even if the data changes or is omitted, like in your case, the fields included in the file should be static.

    You could try using regular expressions in the script task but that can get ugly.

  • November 15, 2012 at 11:12 am

    #1559464

    ziako (11/15/2012)

    Thanks for your suggestions. I do need the field. Its just the field may not be included in some daily files. How would i use the script task editor to remove the comma if there is no field?

    I don't know how you would do it in SSIS but, if I were doing this with Bulk Insert or some such, I'd read the first data row of the file as a blob, count the delimiters, and then decide which format to use for the import. There should be something similar you could to with SSIS.

    --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 22, 2012 at 1:18 pm

    #1561707

    ziako (11/15/2012)

    Thanks for your suggestions. I do need the field. Its just the field may not be included in some daily files. How would i use the script task editor to remove the comma if there is no field?

    I would not worry about altering the file on the fly. Rather, I would provide for two different data flows, one for each type of file, namely the one with a FuelSurcharge field and the one without. Here is what your SSIS package might look like:

    The initial Script Task would use the StreamReader.ReadLine() to read line one of the file so you can check for the FuelSurcharge field. If one exists then you will set the package-level variable User::FileHasFuelSurcharge to true.

    Notice in the picture that the precedence constraints leaving the Script Task have the function icon. I double-clicked on the constraint line and configured them to evaluate not only that the Script Task succeeds, but also to look at the variable User::FileHasFuelSurcharge. When it is true the Data Flow named Import File with FuelSurcharge field will execute, else the Data Flow named Import File without FuelSurcharge field will execute.

    It's the same processing algorithm Jeff explained implemented in SSIS.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • November 23, 2012 at 2:08 am

    #1561790

    Issue sorted now. Thanks Guys.

Viewing 8 posts - 1 through 7 (of 7 total)

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