Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Flat File Importing via SSIS - Best approach?

Go down

Flat File Importing via SSIS - Best approach? Empty Flat File Importing via SSIS - Best approach?

Post  NbleSavage 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

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Anyone?

Post  NbleSavage Mon Dec 06, 2010 7:58 pm

ttt for any suggestions here.

NbleSavage

Posts : 24
Join date : 2010-11-28

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Anyone have a good work-around / solution for this?

Post  NbleSavage Wed Dec 08, 2010 5:58 am

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

NbleSavage

Posts : 24
Join date : 2010-11-28

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  NbleSavage Thu Dec 09, 2010 6:10 am

I just know you have.....

NbleSavage

Posts : 24
Join date : 2010-11-28

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

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

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

http://www.kimballgroup.com

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Again, thank you!!

Post  NbleSavage Thu Dec 09, 2010 8:30 pm

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

NbleSavage

Posts : 24
Join date : 2010-11-28

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty I miss Informatica....

Post  NbleSavage 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

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  John Simon 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

http://jsimonbi.wordpress.com

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  NbleSavage 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

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  John Simon 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

http://jsimonbi.wordpress.com

Back to top Go down

Flat File Importing via SSIS - Best approach? Empty Re: Flat File Importing via SSIS - Best approach?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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