Reorganize index task in SSIS

Hello friends, today we are going to learn about one more SSIS task which is “Reorganize index task”.This task is more useful for DBAs, generally DBAs run a query batch to reorganize indexes on production server in a scheduled manner. Due to DML Operations (insert, update, delete), Indexes get fragmented and we need to defragment the indexes by reorganizing or rebuilding the indexes. Lets see how we can implement “Reorganize index task”.

First of all we will take a “Reorganize index task” in control flow region(As shown below).

We will right click on the task and click on “Edit” menu that will bring a new screen to us(Shown Below).

Now we will configure the server detail for which we want to reorganize the indexes. To configure the server, we will click on the “New” button(shown in the above screenshot) that will open up a new window(As shown in the below screenshot).

In the “Connection Name” field , we will provide the relevant name to the connection(In my case connection name is Myserver). In second field “Select or enter a server name”, we will write the SQL Sever name(In my case server name is Neeraj-PC). We can choose the server name by clicking on the button(encircled by red color) that will bring a new screen with the list of SQL server instances available(shown in the below screenshot).

We will select any one server from the list of servers and click on “OK” button to save the settings. After clicking on “OK” button we are back to the previous screen(As shown below).

We have already defined the “Server Name”, now we will provide the Database name in which we want to reorganize the indexes. To define the database name, we will click on the combo box(indicated above by red arrow) that will bring a new window(Shown below).

Below are the details for all fields available in the above window.

All Databases: If we choose this option, reorganize operation will be performed on all the databases available on the server.

System databases: If we choose this option, reorganize operation will be performed on only System databases(Tempdb, Msdb, Master, Model).

All User databases: if we choose this option, reorganize operation will be performed on all user databases except(Tempdb, Master, Model, Msdb).

These Databases: if we choose this option, we need to select those databases from the list on which we want to perform the reorganize operation.

Ignore databases where the state is not online: We should check this option this will prevent to perform reorganize operation for offline databases.

We will click on the “OK” button to save the settings and we will be back to the original screen.

Now, we need to specify the objects on which we want to perform the reorganize operation. “View” option is listed here to perform reorganize operation on “indexed Views”. I have selected “Table” option in this example and after that we need to click on combo box(indicated by red arrow).

As soon as we click on the combo box, a new window will appear(As shown below).

In the above window either we can specify “All objects” or specific table names. In my case only one table is being shown, reason being I have only one table with index on it. We will click on “OK” button to save the settings and then click “OK” further to close all opened screen. We are done with our package, we will press “F5” key to run the package and below is the result.

Green color of the task indicates that our package ran successfully. I hope this article will help you in your projects, please feel free to ask any question related to this article.