Source to Staging - ETL Tools/Best Practice

View previous topic View next topic Go down

Source to Staging - ETL Tools/Best Practice

Post  daz on 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

View user profile

Back to top Go down

Generic or flexible

Post  Al Wood on 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

View user profile

Back to top Go down

Re: Source to Staging - ETL Tools/Best Practice

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

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

Back to top Go down

Re: Source to Staging - ETL Tools/Best Practice

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