hi we are loading the data from oracle to sql server.using ssis packages(no transforamtions are used in the packages .my few staging tables having more than 100 millions data.we are following below structure 1.drop index 2)loading the Data 3) create index out of 5hrs ETL create index container is taking more than 2 hrs. if i follow this structure will create any issue 1)loading the Data 2) create index . tables are having huge volume of data. pls suggest the best way in the perforamance point of view
My suggestion: 1.drop index 2)loading the Data 3) create index
But, how are you loading your data with SSIS? Did you set a batch size or are you doing a single commit? If you haven't set a batch size, then I'd suggest that you set one. Assuming the table is clustered on one or mor columns, is the data sorted or does the index creation have to re-organize the entire table?