Deploy and Schedule an SQL Server Integration Services SSIS Package Step by Step



By:
Overview

Now that our SSIS package development is finished, we can deploy it to the server. There we can schedule and execute the package as well.

Deploying the SSIS Package

In Visual Studio, right-click on the project and select Deploy.

deploy SSIS project

This will start the SSIS deployment wizard. Keep in mind this will deploy the entire project, with all packages included. If you want to deploy an individual package, you can right-click on the package itself and choose Deploy (since SSIS 2016).

In the first step of the wizard, we need to choose the destination (several steps are skipped since we started the wizard from Visual Studio). Enter the server name and make sure the SSIS catalog has already been created on that server. If you want, you can also create a folder to store the project in.

Select destination in Integration Services Deployment Wizard

At the next step, you get an overview of the actions the wizard will take. Hit Deploy to start the deployment.

Review selections in the Integration Services Deployment Wizard

The deployment will go through a couple of steps:

SSIS deployment successful

The project has now been deployed to the server and you can find it in the catalog:

SSIS project in catalog as seen in Management Studio

Executing an SSIS Package on the Server

To execute the package, simply locate it in the catalog folder, right-click it and hit Execute…

Execute package from SSMS

You will be taken to a dialog where you can edit certain properties, such as the connection managers, parameters if any, the amount of logging and so on.

execute package

Click on OK to start the execution of the package. A pop-up will open asking you if you want to open one of the catalogs built-in reports.

sql server management studio

Click Yes. This will take you to the Overview report, where can see the package has successfully executed.

overview report

To learn more about the catalog reports, check out the tip Reporting with the SQL Server Integration Services Catalog.

Scheduling the SSIS Package with SQL Server Agent

Manually executing packages is one thing, but normally you will schedule packages so your ETL can run in a specific time windows (probably at night). The easiest option is SQL Server Agent. You can right-click on the Jobs node to create a new job:

create new job

In the General pane, enter a name for the job, choose an owner and optionally enter a description:

enter properties job

In the Steps pane, you can create a new job step.

create new job step

In the job step configuration, you can enter a name for the step. Choose the SQL Server Integration Services Package type, enter the name of the server and select the package.

job step config

In the configuration tab, you can optionally set more properties, just like when executing a package manually. Click OK to save the job step. In the Schedules tab, you can define one or more schedule to execute the package on predefined points in time. Click New… to create a new schedule. In the schedule editor, you can choose between multiple types of schedules: daily, weekly or monthly. You can also schedule packages to run only once. In the example below we have scheduled the job to run every day at 1AM, except in the weekend.

job schedule

Click OK twice to exit the editors. The job is now created and scheduled.


Last Update: 8/31/2017






Comments For This Article




Thursday, April 23, 2020 - 2:13:56 AM - Koen Verbeeck Back To Top

Hi Ganga,

you can use the dtexec utility for this:
https://docs.microsoft.com/en-us/sql/integration-services/packages/dtexec-utility?view=sql-server-ver15

Regards,

Koen


Wednesday, April 22, 2020 - 10:16:59 AM - Ganga Back To Top

Hi Koen,

Very good explanation, here would like seek some more additional information. Most of the organization will have individual scheduler tool, so if we need execute these packages from either shell/batch scripts. So please help me to know the process.


Monday, July 08, 2019 - 9:37:57 AM - Ramya Parthasarathy Back To Top

Wonderful post...I learned SSIS very easily because of your blog. Thanks a lotttttttttttt!!!!!



download


get free sql tips
agree to terms


Learn more about SQL Server tools