In SSIS, when defining a fixed flat file length flat file connection, is there a way to programmatically define the columns?

Question

I couldn’t play with my four-year-old yesterday. Why not you ask?

Because I had a 150 column fixed length file that I needed to configure in SSIS for import into a SQL server database.

I know how to do this but not being able to define the columns programmatically is driving me bonkers. It’s worse than watching American Idol reruns.

In the “Flat File Connection Manager Editor” I set the format to “Ragged right” check off “Column names in the first data row”.

Then in the columns tab there is the interface where you define the columns.

I have the columns definitions in an excel spread sheet. I know the column names and the exact length.

But why in the name of Zeus do I have to manually click on that stupid little ruler thing 150 times

I should be able to script this out.

I mean sitting there clicking off the columns in absolutely mind-numbing. What should have taken an hour took me three hours because every three columns or so the editor bounced me back to the beginning of the row forcing me to scroll back to where I was (argh!!!!).

I even opened up the SSIS package.dtsx file to see if I could edit it outside of Visual Studio, however the GUID value next to the DTSID property nixed that brilliant idea.

So my dear StackOverFlow community is there a way to define a fixed length flat file connection programmatically and thereby skip the fun of clicking off 150 columns manually?

Answer 1

Yes: http://blogs.msdn.com/b/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

You'll typically open an existing (template) package through .NET using the Microsoft.SqlServer.Dts.* classes, add the connection manager and the columns and then save the package for further tweaking by hand.