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.
Explanation
Deploying the SSIS Package
In Visual Studio, right-click on the project and select Deploy.
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.
At the next step, you get an overview of the actions the wizard will take. Hit Deploy to start the deployment.
The deployment will go through a couple of steps:
The project has now been deployed to the server and you can find it in the catalog:
Executing an SSIS Package on the Server
To execute the package, simply locate it in the catalog folder, right-click it and hit Execute…
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.
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.
Click Yes. This will take you to the Overview report, where can see the package has successfully executed.
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:
In the General pane, enter a name for the job, choose an owner and optionally enter a description:
In the Steps pane, you can create a 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.
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.
Click OK twice to exit the editors. The job is now created and scheduled.
