Publish to SSIS Catalog using PowerShell

Here is a PowerShell script that I use to provision and setup SSIS Catalogs when I’m demoing SQL Server 2012. It does the following:

  1. Connects to localhost (default instance)
  2. Drops the existing SSIS Catalog
  3. Creates a new SSIS Catalog
  4. Creates a Folder
  5. Deploys a Project
  6. Creates an Environment
  7. Creates three server varaibles
  8. Creates an environment reference for the project

Be careful of step #2 above – this script is great for resetting a demo environment, but you’ll want to modify it if you’re using it for real deployments!

 

11 thoughts on “Publish to SSIS Catalog using PowerShell”

  1. Matt, this is great information but I think there might be one step missing at the very end. After creating the refrence to the Evnironment in the project don't you need to create individual reference between the project parameters and the variables in the environment? I found an example over on the SSIS Blog by some guy or another in the "Scenario #2" example: http://blogs.msdn.com/b/mattm/archive/2011/11/17/

      1. I know 🙂 I was very amused that I started working through a PowerShell script using your example here, got stuck on some errors, and then found the rest of the solution in another article (also written by you). Thanks.

  2. Hey Matt,

    This script is great but I'm running into an issue with executing it from a TeamCity job and I believe it's a permissions based issue.

    When it runs the following command I get an error:

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

    Error:

    New-Object : Exception calling ".ctor" with "1" argument(s): "Failed to connect to server ."

    Is this, in fact, a permissions error when connecting to SSIS? Or could this be another error from SSIS that I am not considering?

  3. Hello Matt,

    I’ve been trying to set up SSIS packages deployment using PowerShell and your script is very useful as a starter, however I’m still having issues with password-encrypted packages. Without any modification to your script, I get the error that “the package is corrupted or the password is incorrect”.

    I tried using the Project class, which is able to read and decryt the ispac file:

    # Load the ManagedDTS assembly
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ManagedDTS”) | Out-Null;

    # Read and decrypt the project file
    $project = [Microsoft.SqlServer.Dts.Runtime.Project]::OpenProject($ProjectFilePath, $ProjectFilePassword)

    $projectStream = New-Object System.IO.MemoryStream
    $project.SaveTo($projectStream)

    # Deploy it to the folder
    $folder.DeployProject($ProjectName, $projectStream.ToArray())

    However, and as expected, the SaveTo method also encrypts the Project when writing to the MemoryStream and I finally get the same error.

    Do you have any idea whether it is possible to deploy a passord-protected package using PowerShell?

    Thanks and regards,
    Awen

  4. Hello Matt,
    Thanks for your really useful script!
    Still using PowerShell, I need to script the part where I update the project with the .ispac in Integration Services, I tried to modify your script commenting the parts who delete the file and SSISDB, but I got some errors…
    I guess I’m supposed to modify theses lines but I don’t know how…
    [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
    $folder.DeployProject($ProjectName, $projectFile)
    Do you know how to update the project ?
    Thanks you

    1. Hi Johan,

      is your package password-protected (see my previous post)? If so, I can tell you I never managed to deploy using the PowerShell API. I had to call the Integration Services Deployment Wizard as a command line in my PowerShell script:

      #region script configuration

      $SsisServer = “localhost”

      $ProjectFileName = “Project.ispac”
      $ProjectFilePassword = “SuperSecret”

      $FolderName = “MyFolder”
      $ProjectName = “MyProject”

      $ISDeploymentWizard = “C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe”

      #endregion

      #region project deployment

      # Create command line arguments
      $DestinationPath = “/SSISDB/” + $FolderName + “/” + $ProjectName
      $ProjectFilePath = $PSScriptRoot + “\” + $ProjectFileName

      $cmd = $ISDeploymentWizard
      $arg1 = “/Silent”
      $arg2 = “/SourcePath:””$ProjectFilePath”””
      $arg3 = “/DestinationServer:””$SsisServer”””
      $arg4 = “/DestinationPath:””$DestinationPath”””
      $arg5 = “/ProjectPassword:””$ProjectFilePassword”””

      #Write-Host $cmd $arg1 $arg2 $arg3 $arg4 $arg5
      & $cmd $arg1 $arg2 $arg3 $arg4 $arg5

      #endregion

      Be careful, I didn0t manage to escape spaces in the DestinationPath argument, and they will cause very obscure errors.

      I hope this helps 🙂

Leave a Reply

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