Flat File Importing via SSIS - Best approach?

View previous topic View next topic Go down

Flat File Importing via SSIS - Best approach?

Post  NbleSavage on Mon Dec 06, 2010 6:02 am

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

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Anyone?

Post  NbleSavage on Mon Dec 06, 2010 7:58 pm

ttt for any suggestions here.

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Anyone have a good work-around / solution for this?

Post  NbleSavage on Wed Dec 08, 2010 5:58 am

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

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  NbleSavage on Thu Dec 09, 2010 6:10 am

I just know you have.....

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  Joy on Thu Dec 09, 2010 5:18 pm

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.
avatar
Joy

Posts : 20
Join date : 2009-02-03
Location : Kimball Group

View user profile http://www.kimballgroup.com

Back to top Go down

Again, thank you!!

Post  NbleSavage on Thu Dec 09, 2010 8:30 pm

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

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  BoxesAndLines on Thu Dec 09, 2010 10:20 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

I miss Informatica....

Post  NbleSavage on Fri Dec 10, 2010 5:21 am

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.

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  John Simon on Sun Dec 12, 2010 5:57 am

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

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  NbleSavage on Sun Dec 12, 2010 10:53 am

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.

NbleSavage

Posts : 24
Join date : 2010-11-28

View user profile

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  John Simon on Sun Dec 12, 2010 6:44 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Flat File Importing via SSIS - Best approach?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum