Flat File Importing via SSIS - Best approach?

My company is frequently faced with the need to import and profile flat files submitted by various prospects and customers. These customers rarely provide us with a format file and when they do it is rarely accurate. Importing CSV files is slightly easier than fixed width files, however even the SQL Import Wizard does not accurately reflect back the file's schema in the new table which it creates as part of the import process. Fixed width files are obviously more difficult, as frequently we'll receive files with 100+ columns, all of which need to be manually "fitted" in order for the import to take place successfully.

Any ideas on the most efficient ways of importing these flat files into SQL would be appreciated.

Peace.

- Savage

ttt for any suggestions here.

Somebody's seen this issue before .....

I just know you have.....

I'm sure someone has run into this problem before. Here are my thoughts on avenues to explore:

1. Look for a 3rd party flat file driver, probably ODBC or OLE DB. If you find a driver that better meets your needs, you can use the SSIS OLE DB source rather than the Flat File source. (You can use an ODBC driver by using OLE DB for ODBC). This won't perform as well as the SSIS Flat File source, but I'm imagining performance isn't your #1 problem.

2. You could write your own custom SSIS data flow source that does exactly what you want. This is probably infeasibe or, more accurately, more development effort than it's worth.

3. You could continue to use the Flat File source, but import the entire row into a single field. Then write a script that parses each data row. There's a script at http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/ that solves a similar (simpler) problem, which would be a reasonable starting point.

4. It's possible that Excel with PowerPivot may be worth investigating. The PowerPivot add-in allows very large (both long and wide) Excel spreadsheets. You can download it for free from Msft, but it only works with Excel 2010. This approach may be worth investigating if what you're trying to do is have a person look at the data, in which case of course Excel is everyone's favorite tool. This certainly isn't the scenario that PowerPivot was designed for, but follows in the venerable tradition of twisting Msft technology, especially Excel, to do whatever needs doing.

Good luck.

You rock, Joy. Thanks again for all your suggestions!!

I don't suppose you have an ETL tool like Informatica. It's pretty much an automated process.

BoxesAndLines wrote:I don't suppose you have an ETL tool like Informatica. It's pretty much an automated process.

Hey B&L (*GREAT* handle, btw )

Unfortunately, no. I've used Informatica before in a "past life" with a big company and it worked well. We're a small company and we've got dat' dere' MSDN so of course it's the entire Microsoft tech stack for us, but as such we're limited to what we can do with OOTB Microsoft tools and our own custom code.

I'm going to load and test a custom SSIS component from CodePlex which I found in the blog linked from Joy's posting above. I'll report-out as to how it fairs for this mundane, yet critical and time-consuming task.

This may sound obvious, but surely the issue is more around process than technology. Why can you not provide your customers with an interface agreement with the attributes you need?

John Simon wrote:This may sound obvious, but surely the issue is more around process than technology. Why can you not provide your customers with an interface agreement with the attributes you need?

At its core, you are exactly correct. Our challenge is that we frequently receive "sample files" from prospects - not clients - and our client management team is reluctant to push-back on said prospect via delivery of and requested adherence to a spec file / a pre-defined interface (although that is precisely what would solve this problem).

In a way I suppose we are looking to compensate via technology for what amounts to a business process problem.

I've been in a similar situation before. The only way to get around it is to convince the business of the extra costs incurred for development and maintenance by creating new packages for each customer/prospect. Also try to convince the sales people that the clients would probably prefer to be given an interface agreement so people on their end don't have to figure out what to give you. Maybe you could convince your bosses to charge development time to the Sales people's cost centre for development if they don't use the an interface agreement. That might change their minds

Good Luck.