none
SSIS column marker problem for flat file ragged right

    Question

  • In Oct 2008, Ken Powers asked why he couldn't add more column markers on a flat file with ragged right after going to the advanced tab.  Matt David responded that that's by design but could be submitted as bug / enhancement.  So, what are my alternatives using SQL Server 2008 R2 if I've created a flat file connection and later need to go back and add more columns or markers?  Is my only course to recreate the whole thing (crazy) or use the advanced tab and create one at a time?


    Dale Krabill 216-252-7300 x6484 American Greetings

    Friday, April 13, 2012 6:47 PM

Answers

  • Hi Bubba Krab,

    Just as Matt David said we couldn't add more column markers on a flat file with ragged right after going to the advanced tab. So that we cannot use advanced tab to create new columns. SSIS cannot make the columns dynamic, as it needs to store metadata of each column as it come through.

    We can use script task to achieve your target,
    1. Create a parent package, using script task to read the flat file, only reads the first line of the flat file to get the number of columns, and the column names. This information can be stored in a variable.
    2. Then the parent package use script task loads the child package programmatically, and updates the metadata of the Source Connection of the child package.

    For more details please refer to the guna's reply in the following link:
    http://stackoverflow.com/questions/4281237/ssis-flat-files-with-variable-column-numbers

    And here is an article about handling Flat Files with Varying Numbers of Columns, please see: http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Friday, April 20, 2012 2:49 AM
    Monday, April 16, 2012 8:34 AM

All replies

  • Hi Bubba Krab,

    Just as Matt David said we couldn't add more column markers on a flat file with ragged right after going to the advanced tab. So that we cannot use advanced tab to create new columns. SSIS cannot make the columns dynamic, as it needs to store metadata of each column as it come through.

    We can use script task to achieve your target,
    1. Create a parent package, using script task to read the flat file, only reads the first line of the flat file to get the number of columns, and the column names. This information can be stored in a variable.
    2. Then the parent package use script task loads the child package programmatically, and updates the metadata of the Source Connection of the child package.

    For more details please refer to the guna's reply in the following link:
    http://stackoverflow.com/questions/4281237/ssis-flat-files-with-variable-column-numbers

    And here is an article about handling Flat Files with Varying Numbers of Columns, please see: http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Friday, April 20, 2012 2:49 AM
    Monday, April 16, 2012 8:34 AM
  • Well, its rather late after this posting, but they way I look at it, this is a GREAT BIG BUG on Microsoft's part. I've got a flat file that is over 2800 bytes long with over 250 columns. It took FOREVER to set up. Right in the middle of it, is some 'filler' and the client has used up some of it to add new data. GUESS WHAT. I go into advanced, and it tells me to click on the ruler to add columns, or right-click and select Delete or double-click on a ruler line to delete it. Does either work? No! WTF #$&*@!#.

    Microsoft, get your stupid act together.


    Doug Bishop

    Thursday, July 18, 2013 11:04 PM
  • I was struggling for 2 days to add more columns. There is no way I would recreate from scratch all that I had already done. I had a file with 160 columns and most of them were already created. I only needed a few. I then found a way to add more columns. If you want to add more columns, don’t click on the ruler to create another column. Instead, go to the advanced tab and click on New to add another column. Give it a size of 10 or any other number. However, it creates 10 positions from the record mark specified by the red bar. Then, you need to drag the bar to where you want it. I know it is a pain. But I found no other way to do it. I hope this helps.

    Jose Freitas

    Friday, August 29, 2014 5:44 PM
  • I was struggling for 2 days to add more columns. There is no way I would recreate from scratch all that I had already done. I had a file with 160 columns and most of them were already created. I only needed a few. I then found a way to add more columns. If you want to add more columns, don’t click on the ruler to create another column. Instead, go to the advanced tab and click on New to add another column. Give it a size of 10 or any other number. However, it creates 10 positions from the record mark specified by the red bar. Then, you need to drag the bar to where you want it. I know it is a pain. But I found no other way to do it. I hope this helps.

    Jose Freitas

    Yes thats true

    I also did it through advanced tab only


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, August 29, 2014 6:34 PM