SSIS Package Deployment

This tutorial instructs how to deploy the SSIS Package created in the Output To Excel onto an Integration Services catalog on the SQL Server. Follow the below steps to do.

  • In the Solution Explorer, expand the SSIS Packages folder
  • Right click on Package.dtsx file -> Deploy Package -> Next -> Next
  • Click on Connect -> Browse -> New folder… -> give it a name e.g. OutputToExcel -> New project -> give it a name e.g. OutputToExcel -> OK -> Next -> Deploy -> Close
  • Open SQL Server Installation Center (SSMS), check that the Package.dtsx can be seen as the below screenshot

  • To run the package from SSMS, right click on Package.dtsx -> Execute

Common errors

An Integration Services catalog (SSISDB) was not found on this server instance

When deploy the SSIS package, if there is an error message below:

An Integration Services catalog (SSISDB) was not found on this server instance (“localhost”). To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services Catalogs node.

Follow the below steps to fix:

  • Open Microsoft SQL Server Management Studio (SSMS)
  • Connect to your SQL Server Database Engine
  • In the Object Explorer window, right click on Integration Services Catalogs -> Create Catalog…
  • Check the Enable automatic execution of Integration Services stored procedure at SQL Server startup option
  • Set the password -> OK

Reference: here

The catalog backup file SSISDBBackup.bak could not be accessed

When create the SSISDB catalog, if there is an error message below:

The catalog backup file ‘C:\Program Files\Microsoft SQL Server\150\DTS\Binn\SSISDBBackup.bak’ could not be accessed. Make sure the database file exists, and the SQL Server service account is able to access it. (Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)

Follow the below steps to fix:

  • Open SSMS
  • Click on the option highlighted to add features to an existing installation -> browse to your SQL Server Installation Media folder -> keep click on Next until you reach the Feature Selection screen
  • Select Integration Services -> Next -> Next to install the Integration Services feature