Deployment from the Command Line

The Deployment Wizard (ISDeploymentWizard.exe) provides a command line interface, which allows you to deploy to the SSIS Catalog without a UI. This is very useful for deploying from scripts, or as part of a batch process. Table 19-1 shows the list of supported parameters. Listing 19-1 provides an example command line that deploys a project (C:SSISProject.ispac) to a folder named MyFolder on a local SSIS Catalog.

Listing 19-1. Deploying a project from the command line

ISDeploymentWizard.exe /Silent /SourcePath:"C:ETLProject.ispac"
/DestinationServer:"localhost" /DestinationPath:"/SSISDB/MyFolder/Project"

Deployment Using Custom Code

The SSIS Catalog has a managed .NET API called the Management Object Model (or MOM). This API allows you to programmatically perform that same management tasks that would normally be done through SQL Server Management Studio (SSMS), including Folder creation, and deployment of projects.

Listing 19-2 provides a sample C# application that makes use of the MOM to create a new Folder in an SSIS Catalog, and deploys a project to it. The core functionality can be found in the Microsoft.SqlServer.Management.IntegrationServices assembly, which is installed with SSMS and found in the Global Assembly Cache (GAC).

Listing 19-2. Deploying a project using the MOM

class Program
{
    const string ProjectFileLocation = @"C:ETLProject.ispac";

    static void Main(string[] args)
    {
     // Connect to the default instance on localhost
     var server = new Server("localhost");
     var store = new IntegrationServices(server);

    // Check that we have a catalog
     if (store.Catalogs.Count == 0)
     {
     Console.WriteLine("SSIS catalog not found on localhost.");
     }

     // Get the SSISDB catalog - note that there should only
     // be one, but the API may support multiple catalogs
     // in the future
     var catalog = store.Catalogs["SSISDB"];

     // Create a new folder
     var folder = new CatalogFolder(catalog,
         "MyFolder",
         "Folder that holds projects");
          folder.Create();

         // Make sure the project file exists
     if (!File.Exists(ProjectFileLocation))
     {
         Console.WriteLine("Project file not found at: {0}",
     ProjectFileLocation);
     }

     // Load the project using the SSIS API
     var project = Project.OpenProject(ProjectFileLocation);

         // Deploy the project to the folder we just created
     folder.DeployProject(project);
    }
}

Deployment Using PowerShell

The SSIS Management Object Model (MOM) is accessible via PowerShell, which makes it possible to fully automate your deployment (and other management tasks) using PowerShell scripts. Listing 19-3 shows the PowerShell version of the simple deployment application from Listing 19-2.

Listing 19-3. Deploying a project using PowerShell

# Variables
$ProjectFilePath = "C:ETLProject.ispac"
$ProjectName = "Project"
$FolderName = "MyFolder"

# Load the IntegrationServices Assembly
$loadStatus = [Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, Version = 11.0.0.0, Culture = neutral, PublicKeyToken = 89845dcd8080cc91")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ... "

# Create a connection to the server
$sqlConnectionString = "Data Source = localhost;Initial Catalog = master;Integrated Security = SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$catalog = $integrationServices.Catalogs["SSISDB"]

Write-Host "Creating Folder" $FolderName " ... "

# Create a new folder
$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "This is a folder description")
$folder.Create()

Write-Host "Deploying" $ProjectName "project ... "

# Read the project file, and deploy it to the folder
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$project = $folder.DeployProject($ProjectName, $projectFile)

Write-Host "All done."

Deployment Using SQL

If you prefer to do all of your database management and deployments using T-SQL, the SSIS Catalog exposes a full management interface through a set of Views and Stored Procedures. Listing 19-4 provides a sample that loads a project file in binary format, deploys it to a folder using the [catalog].[deploy_project] stored procedure, and then queries the status of the deployment from the [catalog].[operations] view.

Listing 19-4. Deploying a project using the SQL API

use SSISDB

DECLARE @ProjectBinary as varbinary(max)
DECLARE @OperationID as bigint

-- load the project file
SET @ProjectBinary =
(
  SELECT *
  FROM OPENROWSET
  (
    BULK 'C:ETLProject.ispac',
    SINGLE_BLOB
  ) as BinaryData
)

-- deploy the project
EXEC [catalog].[deploy_project]
 'MyFolder', -- folder
 'Project', -- project name
 @ProjectBinary, -- binary data
 @OperationID out -- operation id

--
-- Get the status of the last deployment
--

DECLARE @LastDeployment_id bigint;
SET @LastDeployment_id =
(

  SELECT MAX(operation_id)
  FROM [catalog].[operations]
  WHERE operation_type = 101	-- deploy
)
SELECT [object_name], start_time, end_time, [status], [value] =
  case
      when [status] = 1 then N'Created'
      when [status] = 2 then N'Running'
      when [status] = 3 then N'Canceled'
      when [status] = 4 then N'Failed'
      when [status] = 5 then N'Pending'
      when [status] = 6 then N'Unexpected Termination'
      when [status] = 7 then N'Succeeded'
      when [status] = 8 then N'Stopping'
      when [status] = 9 then N'Completed'
  end
FROM [catalog].[operations]
WHERE [operation_id] = @LastDeployment_id