Hi, We have developed a below ETL packages. Master packages - 1 Child packages - 10 Containers in each package - 5 Each container has a data flow from source to destination.Target table has indexing (both clustered and non clustered). We have planned to disbale index in the target table during dataflow and enable the same after completion of dataflow. Now i had below queries while designing. Can we execute disbale index of all tables at master package level and then execute the child packages and enable the index of all tables once child packages is completed? else can we execute disable index at each container and enable index once that dataflow is completed? which one will be best approach? guidance is much appreciated Thanks in advance.

Hi, first things first. Did you already conclude that it will take less time with the drop index + load data + create index? Specially for clustered indexes this last step should take some time. If the best approach is to drop + create indexes and if each table is loaded in only one package/container then I would create + drop the indexes in that package, just to have it better organized because in terms of performance should be exactly the same. David.

Hi, The process has been designed based on source records per day. Also, the process is an increamental load on a daily basis. So the entire data loading process will be done inside a for loop container. The date will be the input for the loop. 1. If we execute diable index at master package, it will drop all the table index irrespective of data load (i.e., some table will not get the data load on that particular day. but still we will disable & enable index for these table at master package level which will take addtional time) 2. If we handle at container level, i can disable & enable index if date variable contains value and then start the loop. This will avoid the above issue and indexing will handled only for the dataload available. We had different views in above points and i suggested the 2nd method as it will avoid the indexing(enable/disable) for the tables which will not have the data load. I thought it would be best to get the expert guidance.

Place a SQL task in each of respective package to alter the index structure of the tables. But, if only performance is the reason, why you plan to drop and create index. There are better ways to bulk load One such is here http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar

Place a SQL task in each of respective package to alter the index structure of the tables. But, if only performance is the reason, why you plan to drop and create index. There are better ways to bulk load One such is here http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar