What are some Pros and Cons of ETL from Excel into my SQL Server DW?

View previous topic View next topic Go down

What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  BI Consultant on Thu Aug 11, 2011 1:04 pm

I have a data model for my DW and the data source for this DW is Excel, i.e., there's no operational data source, all the data is in several Excel files, updated weekly. What are some things to keep in mind if I have to ETL using SSIS packages into my data warehouse?

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  ngalemmo on Thu Aug 11, 2011 1:34 pm

Your biggest problem will be locking down the format of the sheets. Getting a lot of garbage data would be second on my list.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

Post  Mike Honey on Thu Aug 11, 2011 9:34 pm

I agree with ngalemmo's first two.
Third on my list would be accessing files (typically on a secured file share) from the ETL process (typically running under a service account on a different server).
Fourth on my list would be file locking. If someone leaves an Excel window open (or has an Excel crash) you usually find your SSIS process fails.

Overall in my experience it can be done, but you need to put serious thought into the design and build of your control flow and data staging to make it as resilient as possible.

Good luck!
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

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

Back to top Go down

Re: What are some Pros and Cons of ETL from Excel into my SQL Server DW?

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