Source to Staging - ETL Tools/Best Practice

[daz]

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.

[Al Wood]

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

[John Simon]

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.

[Sponsored content]