Importing multiple UTF-8 files with varying number of columns

Question

So we all know you can't bulk insert with an UTF-8 text file into SQL Server 2008. Then its a flat file source to a database for me. However I have 200 files. How can I import these all at once instead of creating a SSIS task for each different set of column numbers?

For example create a SISS data flow task for the ones with 30, a different SSIS task for the ones with 34 columns etc.

Note that a FOR EACH LOOP CONTAINER does not work. I tried and it failed .

Also after column 20 the order of the columns in the text file is different in some of the files as well .

Andy Leonard Andy Leonard · Accepted Answer · 2012-05-04T20:04:25

For 200 files with differing schemas, you will want to consider automating SSIS package (and Data Flow Task) creation. Once a Source Adapter (Flat File or other) is added to a Data Flow Task, the schema of source is coupled to the Data Flow. You can see this in action if you create a Flat File Source connected to one of your files, add some other component to the Data Flow Task, connect a Data Flow Path from the Source Adapter to the component, and then open the Metadata page on the Data Flow Path Editor. From inside the Business Intelligence Development Studio, you cannot dynamically modify Data Flow Path schemas, but you can dynamically create them from a .Net application or by using a third-party package-generation platform.

Hope this helps, Andy

Importing multiple UTF-8 files with varying number of columns

1 Answers