There are an infinite number of ways to load a table with data however I believe most people would agree with me in saying that a table without indexes will load faster than one with indexes. Microsoft talks about performance in loading tables in the guide (The Data Loading Performance Guide). In that guide they say the following: “Before you load data into an empty table, it is always a good idea to drop or disable all indexes on the table. After the load is done, re-create or re-enable the indexes.”
In SSIS there are a couple of ways you can do this, the easiest way is to use an Execute SQL Task to drop the index(s) before your load and then use a second task after your load to re-create the index(s). While this is certainly a good option it causes a problem in production. A code change is required for each change to an existing index or new index is created. In order to get around this you need to drop all indexes on a table and re-create them after the load. The question becomes how do you know what indexes exist to drop and after you drop them how do you re-create them. Finally you might ask why don’t we just disable the indexes.
We drop and re-create the indexes vs. disabling them because if a clustered index is disabled it will prevent an Insert (Guidelines for Disabling Indexes and Constraints).
To drop and recreate the indexes we can use SMO (SQL Management Objects) to obtain a list of indexes for a particular table along with a SQL script to create them. Using an object variable in SSIS we can store the SQL scripts for re-creation after the data load. To do this your package would look something like this:
In this example we have a completion constraint between the Data Flow Task and the second script that re-creates the indexes because regardless if the Data Flow ends in success or failure we want to put the indexes back.
When you build your script task you will need the SMO Libraries to access the SQL Server via SMO and the generic collections library to build the list of SQL objects.
The code below will script out the indexes and drop them.
The script below will re-create the Indexes: