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