Deploying the package to a SQL Server


To deploy the newly created package to a SQL Server (SSISDB Catalog), rom within Visual Studio, we right-button clock on the root of the package and select DEPLOY.

The wizard opens. in our example, we select a deploy target of ‘SSIS in SQL Server’. fill in the server name, click ‘Connect’ and then edit the path where the package will be deployed to your SSID Catalog.

Now hit ‘Deploy’

Review and execute package in SSMS

Switch back to SSMS and refresh the view. you will find your new package now exists

Execute the package manually and when asked if you want to see teh report, select YES.

Report shows result and other useful information

Automating package execution using SQL Agent job

Create a new SQL Agent Job called ‘SSIS – BackupAndArchive’. It is a good idea to name tehse jobs with SSIS at the front.

Add a new step and fill in all fields. setting Type: to SQL Server Integration Services Package is essential.

Add a schedule and click OK

Test by manually kicking the job off

Trouble shooting Issues.

If the execution fails, view the report from the SSIS Catalog

Select “All Messages” from the failed step.

Next Screen shows the cause of the error. The file move hit a Windows OS file permissions issue because SQL Agent jobs executed by Service owner by default.

Once the Windows file permission issue was fixed on C:\MSSQL_Backups and C:\MSSQL_Archive, the SQL Agent job ran successfully.

Leave a Reply

Your email address will not be published. Required fields are marked *