need help to build and deploy ssis packages to Integration Services catalog

Question

I was following this interesting post regarding building SSIs project on Devops.

• Continuous Integration for BI in VSTS: Splitting Build Steps by Project Type

Building worked fine, but I also need to deploy the ispac file to either local Integration Services catalog or on an Azure SQL DB integration catalog.

The ssisbuild tool can be used in a PowerShell task on Azure Devops and building works fine. But when I try using ssisdeploy I got problems.

here is my code (I use nuget tasks to download SSISBuild so it can be run on building and deploy. Below you see my code. Actually I don't need to find all ispac files since I just have one SSIS project. Hope any one can help me out here

Regards Geir

Get-ChildItem -Filter *.ispac -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object {
  &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.3.0\tools\ssisdeploy" $_.FullName -Catalog SSISDB -Folder Test -ProjectName Testproject 

  if ($LASTEXITCODE -ne 0) {
    throw "Build failed.";
  }
}

Answer 1

I usually follow these steps on SSMS to deploy and schedule my SSIS Package on server:

1. Create a Catalog inside Integration Services-Catalog.
2. Right click on this new Catalog and create a new folder inside it

3. After creating the folder you will have two sub-folders automatically created (Projects and Environment).

Now Right click on the Project folder and add a project to it (from file system directory).

4. After this you can use SQL Server-Agent to add a new job and use the above added catalog
   
   

Answer 2

For deploying you need more values like Server, Username, Password etc. take a look on https://www.nuget.org/packages/SSISBuild/#

A command line utility that deploys an SSIS deployment package to an SSIS catalog.

ssisdeploy [Ispac File] -ServerInstance <ServerInstanceName> -Catalog <CatalogName> -Folder <FolderName> -ProjectName <ProjectName> [-ProjectPassword <ProjectPassword>] [-EraseSensitiveInfo]

Ispac File: Full path to an SSIS deployment file (with ispac extension). If a deployment file is not specified, ssisdeploy searches current working directory for a file with ispac extension and uses that file.

-ServerInstance: Required. Full Name of the target SQL Server instance.

-Catalog: Name of the SSIS Catalog on the target server. If not supplied, then SSISDB value is used.

-Folder: Required. Deployment folder within destination catalog.

-ProjectName: Name of the project in the destination folder. If not supplied, then deployment file name is used.

-ProjectPassword: Password to decrypt sensitive data for deployment.

-EraseSensitiveInfo: Option to remove all sensitive info from the deployment ispac and deploy all sensitive parameters separately. If not specified then sensitive data will not be removed.

ssisdeploy.exe sample.ispac -ServerInstance dbserver\instance -Catalog SSISDB -Folder SampleFolder -ProjectName Sample -ProjectPassword xyz -EraseSensitiveI