Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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!!!!!


Learn more about SQL Server tools