Layout File for a Fixed-Length Flat File

Hi,

Does the flat file connection format (Fixed Width) have anyway to include a file layout to define the columns. We have different files which contain over a hundred columns.

Or is there any other way to do this. Script Component?

Thanks,

ShantiThe one option is to have a template package and copy the flat file connection from that package to your new package|||If I understand the question, you want to have something like a format file that the flat file connection manager UI can read to create the files.

We don't have a way to do that though we wanted to get it in. If you have hundreds of columns, I suggest writing a quick program that creates such a flat file connection manager using the runtime object model. There's a sample installed that helps you create a package, connection managers, data flow, etc. which might be a good place to start.

regards,
ash|||Yep, Ash you understood correctly. Thanks for the feedback.

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.