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

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

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/

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.

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.