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