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

Source to Staging - ETL Tools/Best Practice

3 posters

Go down

Source to Staging - ETL Tools/Best Practice Empty Source to Staging - ETL Tools/Best Practice

Post  daz Tue Dec 07, 2010 8:55 am

Hi,

We are running within a Microsoft SQL Server 2008 R2 environment. We have a number of systems that we need to incorporate in our Data Warehouse. Currently we use SSIS to extract incremental data from our source database to our staging database.

Recently we've been asked to write a generic SSIS process that we can employ to bring online other systems.

I was wondering what peoples views were on this. I understand there are a number of ETL tools available in the market place both commercial and open source.

Could anyone recommend a best practice approach for this? And potentially recommend ETL Tools that could help us with the source to staging process?

Many thanks in advance,

Darren.

daz

Posts : 1
Join date : 2010-12-03

Back to top Go down

Source to Staging - ETL Tools/Best Practice Empty Generic or flexible

Post  Al Wood Thu Dec 16, 2010 1:40 pm

Hi,

I wrote a Stored Proc that imports a list of ~50 tables verbatim. It works well, but it does no transformation, and only imports from one SQL server. I tried SSIS but it needs a fixed field list.

Anyway, what if you have multiple diverse data sources? SSIS is flexible for a reason.

Best practice: Develop a template SSIS package, and write a procedure to use it.

Al Wood

Al Wood

Posts : 46
Join date : 2010-12-08

Back to top Go down

Source to Staging - ETL Tools/Best Practice Empty Re: Source to Staging - ETL Tools/Best Practice

Post  John Simon Thu Dec 16, 2010 5:49 pm

Are you loading from files or directly from the databases?

If you're loading files, you may want to create a bulk load process. Once you have the bulk load template you just need to create a format file for each source file. This is the way I've done it in the past and it works really well - it can cut down on a lot of time for loads.

John Simon

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

http://jsimonbi.wordpress.com

Back to top Go down

Source to Staging - ETL Tools/Best Practice Empty Re: Source to Staging - ETL Tools/Best Practice

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