Source to Staging - ETL Tools/Best Practice
3 posters
Page 1 of 1
Source to Staging - ETL Tools/Best Practice
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.
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
Generic or flexible
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
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
Re: Source to Staging - ETL Tools/Best Practice
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.
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.
Similar topics
» Is it a best practice that Data warehouse follows the source system data type?
» ETL Tools please
» Suggestions for BI tools
» Looking for suggestions on ETL tools for DW/BI Project
» Best BI Software for Kimball Method?
» ETL Tools please
» Suggestions for BI tools
» Looking for suggestions on ETL tools for DW/BI Project
» Best BI Software for Kimball Method?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum