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
Comments
Anonymous
December 04, 2014
Hey, do we need to close connection when deployed using c#?Anonymous
February 05, 2015
This is great! There is a typo however. Consider this code in the Powershell script: =========================================================== #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() } ======================================================= It shound't have a "!" after the if. You should only drop it if it exists, not if it doesn't, like this: #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() }Anonymous
March 25, 2015
Thanks for this great script. I was trying to find PowerShell code to drop and replace the folder and this was extremely helpful. Thanks to jkbbr5492 also for his or her comment. It would have taken me a long time to figure out why that part wouldn't work. Thanks again for sharing this.Anonymous
August 13, 2015
How do we pass in a project password to a ssis project that is encrypted with a password?