none
Delimited flat file with no data in final column - won't load into table

    Question

  • Hello,

    I have an SSIS package that's reading in a flat file that's column delimited with ||.  The last column on the last line has no data so the line ends with ||.  When I try to load the file into a database table, it interprets the last line as partial and does not load it into the table.  Any ideas?  I tried setting the RetainNulls property in the source and Keep Nulls in the destination to true but it didn't help.

    Thursday, March 08, 2018 8:34 PM

All replies

  • Hi there,

    What SSIS version (2012 or newer has somewhat different logic for that)?

    And how did you configure the flat file Source?

    Lastly, what error it produced?


    Arthur

    MyBlog


    Twitter

    Thursday, March 08, 2018 8:51 PM
    Moderator
  • Unfortunately it's an older package in VS 2008 on a 2008 SQL Server.  The flat file source is using a {CR}{LF} as a row delimiter and {|}{|} as a column delimiter.  When I preview the source file, I get the message:

    TITLE: Microsoft Visual Studio
    ------------------------------

    The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly.

    ------------------------------

    But the data looks ok.  Here is a sample of the file (2 lines):

    20180221||18||||||||||E0||||||||||||||||||||||||||||||||

    20180221||19||||||||||E0||||||||||||||||||||||||||||||||

    And it only writes the 1st line to the table

    Thursday, March 08, 2018 9:01 PM
  • {|}{|} is the problem, not SSIS 2008

    the delimiter supposed to be just one {|}

    Based on what you explained. Thus the issue you get.


    Arthur

    MyBlog


    Twitter

    Thursday, March 08, 2018 9:43 PM
    Moderator
  • The || delimiter works fine splitting the columns.  The only issue is when the last line ends with the delimiter.
    Thursday, March 08, 2018 9:51 PM
  • Must be {||}

    Arthur

    MyBlog


    Twitter

    Thursday, March 08, 2018 10:05 PM
    Moderator
  • Actually, that made it worse - tried to put entire file into 1 column
    Thursday, March 08, 2018 10:15 PM
  • This is so odd, I tried the Data Import Wizard,

    I see 45 columns if my delimiter is "Vertical Bar {|}"

    With Vertical Bar {||} it is one column.

    And it loaded your example into a table as is. Perfect.

    How did you design your package?


    Arthur

    MyBlog


    Twitter



    Friday, March 09, 2018 2:50 AM
    Moderator
  • Those tasks are part of a larger package.  I didn't create the original package, just made modifications to it.  It used to use a fixed format file and the vendor changed it to double-pipe delimited.  If I manually edit the file and append a space and a CR LF to the last line, everything is fine.  Unfortunately it's an automated process so I need to do that within the package.
    Friday, March 09, 2018 1:25 PM
  • OK. If the issue has been resolved please mark the appropriate reply as the answer.

    Thank you!


    Arthur

    MyBlog


    Twitter

    Friday, March 09, 2018 2:14 PM
    Moderator
  • What would be the best way to add a blank to the last field in the row (between the final delimiter and the CR LF) ?
    Friday, March 09, 2018 3:20 PM
  • I do not see such a need, but if you insist, it'll be a Script Task that appends it using C#/VB. Very easy to do.

    Arthur

    MyBlog


    Twitter

    Friday, March 09, 2018 3:52 PM
    Moderator
  • I appreciate your taking the time to help me with this.  I think the best way to approach this would be to check for the final column having a value and if not, give it a value of a blank.  I'm relatively new at working with SSIS packages and never encountered an issue like this but I'm guessing there is a way to do it.

    Here is my line in Notepad++

    Friday, March 09, 2018 4:20 PM
  • Here I found you C# code that does all you need, but still needs to be revised.

    Arthur

    MyBlog


    Twitter

    Friday, March 09, 2018 10:02 PM
    Moderator
  • Thank you, that worked.  I appreciate your help.
    Tuesday, March 13, 2018 11:58 AM