One vs Multiple Packages
I am developing a solution in SSIS to move data from multiple tables in Oracle to a SQL Server data mart. The data does not have to be imported in any particular order. Some of the tables are small and take very little time to move but a
few others can have millions of records and, if ran via a SQL Openquery, would take up to 10 hours.
I am try to determine what the best approach is in putting together this solution. An older SSIS solution that I have has a .dtsx package for each table. Is that standard practice or is it better to move data from multiple tables into one .dtsx
package?Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D
November 14th, 2011 4:10pm
1) Use the
Oracle connector by Attunity
2) Use one package. Do not connect the "loads" sequentially and they will run in parallel.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 4:45pm
I am developing a solution in SSIS to move data from multiple tables in Oracle to a SQL Server data mart. The data does not have to be imported in any particular order. Some of the tables are small and take very little time to move but a
few others can have millions of records and, if ran via a SQL Openquery, would take up to 10 hours.
I am try to determine what the best approach is in putting together this solution. An older SSIS solution that I have has a .dtsx package for each table. Is that standard practice or is it better to move data from multiple tables into one .dtsx
package?
Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D
The standard Data Flow Task has static metadata defined with the designer. It is impossible to use it with varying table structures. If you can use third-party solutions, check the commercial CozyRoc
Data Flow Task Plus. It is an extension of the standard Data Flow Task, with support for dynamic columns at runtime. You can process all your tables with a single package and Data Flow
Task. No programming skills are required.
Btw I think you will find this
post interesting.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
November 15th, 2011 12:02am
Hi,
it is a good practice to logically divide the ETL process into steps/components and implement a separate package per step/component. This will reduce complexity, allow for reusability and give you more flexibility. Another point is design time simplicity
and maint. One complex package will be harder to implement, it will be slower in Visual Studio designer, it will be harder to test and bugfix.
Basically it will depend on the complexity/number of tables and load type. If you have just few tables to load without complex transforms then you can go for one package. Otherwise consider the approach with multiple packages.
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2011 6:26am
Hi,
it is a good practice to logically divide the ETL process into steps/components and implement a separate package per step/component. This will reduce complexity, allow for reusability and give you more flexibility. Another point is design time simplicity
and maint. One complex package will be harder to implement, it will be slower in Visual Studio designer, it will be harder to test and bugfix.
Basically it will depend on the complexity/number of tables and load type. If you have just few tables to load without complex transforms then you can go for one package. Otherwise consider the approach with multiple packages.
November 15th, 2011 2:22pm