none
Using SSIS to import a flat file that is fixed width

    Question

  • I have been using MS Access to import fixed-width files.  Once the files are imported, I then import them to SQL.  The reason that I import the file in to Access first is because I have "Saved Import Specs" on Access.  I am trying to eliminate the need for Access, and would like the ability to import directly into SQL Sever from my fixed-width file.

    I am using the Flat File Connection Manager Editor, and have entered the widths for the various fields under the Advanced Section.  The problem that I am running into has to do with the "starting point" of the Editor.  I need to be able to configure the first field to use a width of 1-8 charter's (for a total of 8 charters).  I configured the first field to have an InputColumnWidth of 8.  When I view the preview, it looks like The Connection Manager is using charter 8 as the break point.  In MS Access I specified a length of 1-8.

    After entering all of my fields (approx 30,) I am left with an ending value of 349, the ending value is supposed to be 350.

    When I look at my Columns view I can see that for the most part the first row is properly displayed, but the next rows have black square boxes and spaces before the beginning of the next fields.  I will illustrate below with @ being used as the "black square"

     

    MARY

     @@MARY

           @@MARY

                 @@MARY 

     

     

    Thanks for any comments.

    Cheers

     

    Tuesday, May 10, 2011 7:53 PM

Answers

  • I figured out how to fix this issue.  I needed to add an extra column with a width of 3 to account for the extra characters that SSIS was putting into the import specs.
    • Marked as answer by Easton IT Wednesday, May 11, 2011 5:58 PM
    Wednesday, May 11, 2011 5:58 PM

All replies

  • Did you set  CRLF as your row delimiter (if the file has this as its row delimiter)?

    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 10, 2011 8:00 PM
    Moderator
  • I have set CRLF as the the delimiter.  I have also tried not having a delimiter specified and that did not work either.

    Thanks for your reply.

    Wednesday, May 11, 2011 1:11 PM
  • Seems you have the rolling record issue. Those weird chars are probably the CRLFs.

    I think you need to revise how you set the fixed lengths. Also just in case analyze your file if it does indeed have the CRLF as its line delimiter, if yes then you have to use it. To find out use any Hex editor or Notepad++ with View-> Show All Symbols-> Show all chars. The revise the column widths.


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, May 11, 2011 1:35 PM
    Moderator
  • Wait, you know, your file is ragged right, not fixed length, thus you need to use Ragged Right with {CR}{LF} as row delimiter in the Flat File Connection Manager when you setup the Flat File Source in your DFT.

    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, May 11, 2011 1:45 PM
    Moderator
  • I figured out how to fix this issue.  I needed to add an extra column with a width of 3 to account for the extra characters that SSIS was putting into the import specs.
    • Marked as answer by Easton IT Wednesday, May 11, 2011 5:58 PM
    Wednesday, May 11, 2011 5:58 PM
  • Thank you for this.  I spent hours trying to figure out what was causing this issue.  Does SSIS always do this?  Cause I have two files for different import processes.  One file it adds the spaces two, the other one it does not.  Just a curiosity.

    Wednesday, September 18, 2013 1:57 PM