SSIS 2012 Automation – Part 1: Deploy SSIS Project to SSIS Catalog

I recently ran into few cases where customer needed to automate SSIS Project configuration and job creation and I had search whole web to finally reach somewhere.

I think it would be nice to have whole procedure documented so others do not have to search extensively to achieve this.

Also I have created C# code as well as Powershell code to do same so that one can choose what he want to do.

C# and Powershell are kind of similar but It was first time I was trying something powershell so took some time.

I’m planning to keep this in 3 parts

1) Deploying project into catalog

2) Create environment & Configuring project to use that environment

3) Creating Job to run this package which is in SSIS Catalog and assign schedule to it.

This is first part where we will be deploying SSIS Project to SSIS Catalog

Code has comment added into it so that it becomes easier to understand.

I have hard coded all names but you can choose to provide those values at runtime

Pre-requisite:

You need to install SMO dlls and should have SQL 2012 shared component installed

SMO: Search for Microsoft® SQL Server® 2012 Shared Management Objects on

https://www.microsoft.com/en-us/download/details.aspx?id=29065

Also Install Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 from same location.

You should have below references added into project

1) Microsoft.SqlServer.ConnectionInfo

2) Microsoft.SqlServer.Dts.Design

3) Microsoft.SQLServer.DTSRuntimeWrap

4) Microsoft.SqlServer.Management.IntegrationServices

5) Microsoft.SqlServer.Management.Sdk.Sfc

6) Microsoft.SqlServer.Smo

7) Microsoft.SqlServer.SqlEnum

Import below namespaces into cs file

using Microsoft.SqlServer.Management.IntegrationServices;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo.SqlEnum;

C#:

//Connect to SQL server which has SSIS Package

Server SMO_Demo = new Server("dil\\denali");

//Connect to Integration Service

IntegrationServices Is_Demo = new IntegrationServices(SMO_Demo);

//Check if catalog is already present, if not create one

if (Is_Demo.Catalogs["SSISDB"] == null)

{

new Catalog(Is_Demo, "SSISDB", "P@ssword1").Create();

}

Catalog ssisdb = Is_Demo.Catalogs["SSISDB"];

//Check if Folder is presnet or not, if not create one

if (ssisdb.Folders["Test Folder"] == null)

{

new CatalogFolder(ssisdb, "Test Folder", "From code").Create();

}

CatalogFolder ctfolder = ssisdb.Folders["Test Folder"];

//Check if project is already deployed or not, if deployed deop it and deploy again

if (ctfolder.Projects["Integration Services Project1"] != null)

{

ctfolder.Projects["Integration Services Project1"].Drop();

}

else if(ctfolder.Projects["Integration Services Project1"] == null)

{

ctfolder.DeployProject("Integration Services Project1", System.IO.File.ReadAllBytes("D:\\sample projects\\VS\\Integration Services Project1\\Integration Services Project1\\bin\\Development\\Integration Services Project1.ispac"));

}

//Access deployed project

ProjectInfo project = ctfolder.Projects["Integration Services Project1"];

Console.WriteLine(project.LastDeployedTime.ToString());

Console.ReadLine();

Powershell Code:

#Connect to SQL server which has SSIS Package

$sqlInstance = "dil\denali"

$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"

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

#Connect to Integration Service

$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection

#Check if catalog is already present, if not create one

if(!$ssisServer.Catalogs["SSISDB"])

{

(New-Object Microsoft.SqlServer.Management.IntegrationServices.Catalog($ssisServer,"SSISDB","P@ssword1")).Create()

}

$ssisCatalog = $ssisServer.Catalogs["SSISDB"]

#Check if Folder is already present, if not create one

$ssisFolderName = "Test From Powershell"

if(!$ssisCatalog.Folders.Item($ssisFolderName))

{

(New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($ssisCatalog,"Test From Powershell","Powershell")).Create()

}

$ssisFolder = $ssisCatalog.Folders.Item($ssisFolderName)

#Check if project is already deployed or not, if deployed deop it and deploy again

$ssisProjectName = "Integration Services Project1"

if(!$ssisFolder.Projects.Item($ssisProjectName))

{

$ssisFolder.Projects.Item($ssisProjectName).Drop()

}

if(!$ssisFolder.Projects.Item($ssisProjectName))

{

$ssisFolder.DeployProject($ssisProjectName,[System.IO.File]::ReadAllBytes('D:\sample projects\VS\Integration Services Project1\Integration Services Project1\bin\Development\Integration Services Project1.ispac'))

}

#Access deployed project

$ssisProject = $ssisFolder.Projects.Item($ssisProjectName)

Write-Host $ssisProject.Name

I hope this doesn’t give any error.

Now you should see new SSIS Catalog Folder with Projetc and package file.

In next part we will create environment reference and Assign that to peoject and package level parameters.

Do reply with your suggestions and comments and if any then questions well!!!

Author : Dilkush(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Debarchan(MSFT) SQL Developer Engineer, Microsoft