Create, Deploy, and Execute the SSIS package using SQL Server Agent

Total: 1 Average: 5

This article explains how to create an SSIS package using SQL Server Data Tools, deploy it with the Integration Service deployment wizard, and automate its execution through the SQL Server Agent job.

Practical creation, deployment, and execution of SSIS package

To demonstrate the process, we will perform all following jobs in practice:

  1. Create an SSIS package that rebuilds all the user database tables indexes.
  2. Deploy the SSIS package in the Integration Services catalog.
  3. Create an SQL Server Agent job to automate the execution of the SSIS package.

We can create the integration services package using SSDT for Visual Studio 2017. You can download it here.

Once it is downloaded and installed, create a New SSIS Project.

Open SQL Server Data Tools > File > New > Project:

Practical creation, deployment, and execution of SSIS package - Сreate a New SSIS Project

On the New Project dialog box, select Integration Services Project,and name the Project:

Practical creation, deployment, and execution of SSIS package - New Project dialog box

Drag the Rebuild index task from the SSIS toolbox and drop it on Control Flow designer. Rename it to Rebuild All Index.

Practical creation, deployment, and execution of SSIS package. Rebuild index task

Here we need to configure the rebuild index task. Double-click it, and the Rebuild Index Task dialogue window will open.

First, click on New to create a new connection to the server.

Practical creation, deployment, and execution of SSIS package. Rebuild Index Task - Create a new connection to the server

It will open the Connection Properties Dialogue window.

Specify the connection name, the server name, and the authentication method to logon to the server.

Practical creation, deployment, and execution of SSIS package. Connection Properties Dialogue window

Click OK to save the settings, and close the window.

Once the connection is established, the databases’ list fills in the Database(s) drop-down box.

In this demo, the package must rebuild the index of all databases. Click on Database(s) and select All user databases from the drop-down menu.

We want to exclude the databases that are not online. Therefore, check the Ignore databases where the state is not an online checkbox in the same window.

Practical creation, deployment, and execution of SSIS package. Rebuild Index Task - Ignore databases where the state is not an online checkbox

After choosing the databases, you receive access to the configuration options. The default options are reasonable and suitable for our goals, but you can configure additional settings in your particular case:

  1. Free space options:
    • Default free space per page: it drops and recreates the table index with the default FILL factor.
    • Change free space per page to. It drops and recreates the table index with the FILL factor specified by the user. 
  2. Advanced options:
    • Sort TempDB.
    • Keep the index online during the index rebuild operation. (Supported in enterprise edition)
    • Set the MAXDOP value to run the index rebuild in multiple threads.
  3. You can optimize the index rebuild operation by configuring the following options:
    • Rebuild the index only if the fragmentation is higher than the value specified in fragmentation> textbox.
    • Rebuild the index only if the page count is higher than the value specified in the Page Count > textbox value.
    • Rebuild the index only if it is used before the number of days specified in Used in the last textbox.

Note that you can select one of those options or combine several of them.

Click OK to save the configuration.

Practical creation, deployment, and execution of SSIS package. Rebuild Index Task Settings window

Now, let’s deploy the SSIS package.

Hover on Project > Deploy:

Practical creation, deployment, and execution of SSIS package. Deploy the SSIS package

The Integration Services Deployment Wizard starts.

On the first screen, you can see the list of tasks that the wizard will perform. You can skip it – checkmark Do not show this page again at the bottom of the window, and proceed to Next.

Practical creation, deployment, and execution of SSIS package. Integration Services Deployment Wizard

On the Select Deployment Target screen, specify the target.

You can choose either the SQL Server integration services hosted on the on-premise server or the integration services in the Azure Data Factory option.

We have installed integration services in on-premise SQL Server. Hence, select SSIS in SQL Server. Click Next.

Integration Services Deployment Wizard - Select Deployment Target screen

We get to the Select Destination screen.

Specify the server’s name where you have installed SQL Server integration services and provide the connection credentials (Screen 1). Note that you must specify the path to the integration services project in the SSISDB catalog at this stage.

Click on Browse (Screen 1) to locate the project in the SSISDB catalog and navigate to the SSISDB folder (Screen 2). Click OK.

Integration Services Deployment Wizard - Select Destination screen

Click Next and proceed to the Review window.

Here, you can verify the Source and destination details. After that, click on Deploy.

Integration Services Deployment Wizard - Review window

Once the project deploys successfully, click Close to complete the task in the Integration Services Deployment Wizard.

Integration Services Deployment Wizard - Results window

You can view the package under the integration service catalog folder of SQL Server Management Studio.

Open SQL Server Management Studio > Connect to the database engine > expand the database instance > Expand Integration Services Catalogs. Then, expand SSISDB > Index Maintenance

Integration Services Catalogs

Schedule the SQL Server Integration Service Package execution

The SSIS package execution scheduling requires an SQL Server Agent job.

  • The SSIS package execution scheduling requires an SQL Server Agent job.
  • Expand SQL Server Agent and Right-click on Jobs. Select New Job.
  • In the New Job dialog box, enter the desired name in the Name field. Click on Steps to add the job step.
  • Click on New.
Schedule the SQL Server Integration Service Package execution. SQL Server Agent job

You will get to the Job Step Properties dialog window.

  • Provide an appropriate job step name and select SQL Server Integration Services Package from the Type drop-down box.
  • Select SQL Server Agent Service Account from the Run as drop-down menu. We do it because the SSIS package should execute through the SQL Server Agent Service account.
  • Select SSIS Catalog from the Package source drop-down menu, as we’ve deployed the package in the SSIS catalog. Hence
  • Enter the Hostname of the server on which we have installed the integration service.

Note that we must enter the location of the package in the SSISDB catalog. To do that, click on the (…) button (see the below screenshot). A dialog box Select an SSIS package opens. There, select an appropriate SSIS package and click on OK.

Schedule the SQL Server Integration Service Package execution. Job Step Properties dialog window

When the configuration is complete, click OK to save the job step.

Schedule the SQL Server Integration Service Package execution. Job Step Properties dialog window configuration

The job must execute daily at 1:00 AM.

To configure the schedule, click on Schedules > New:

Schedule the SQL Server Integration Service Package execution. New Job Schedule dialog box

In the New Job Schedule dialog box that appears, enter the desired schedule name in the Name textbox.

Choose Daily from the Occurs drop-down box and enter 02:00:00 in Occurs once at the textbox:

Schedule the SQL Server Integration Service Package execution. New Job Schedule dialog box settings

Click OK to save the schedule. Then click OK in the New Job dialog box to save the Agent job.

Test the SQL Job

We test the job by executing it manually. Right-click on the job and select Start Job at Step.

Test the SQL Job - Start Job at Step

If the job completes successfully, you can see the success dialog box shown in the below screenshot.

Test the SQL Job - success dialog box

Summary

This article demonstrates the creation of an SSIS package to rebuild all the SQL Server user database indexes. It also covers the cases of the package deploy in the SQL Server integration services catalog. Additionally, we’ve explained how to automate the SSIS package’s execution through the SQL Server Agent Job.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.