Based on an article of Nat Sundar I implemented another solution to automate build and deploys of SSIS projects via project deployment model, with a higher version of Visual Studio and SQL Server Data Tools. I describe the process here in this post. For the deployment I have made possible to configure all SSIS Parameters, project and package via Power Shell. Let us start.
1. Pre-requisites:
1.1. SQL Server Data Tools 2015 (SSDT2015), you can download them here.
1.2. Visual Studio using CLR 4.0 or above to build an library (assembly).
1.3. Power Shell 4.0 (Scripting the deploy)
1.4. SSIS solutions with Project Deployment Model, implies SSIS > 2008
1.5. Connectivity between deploy server and SSIS Server, protocols TCP and TDS, port 1433.
Note: I have tried to do this using Visual Studio 2017 and SSDT 2017 Preview, because our build server is a headless server and the offline installation of SSDT 2017 Preview did not had success (Installation of SSDT 15.3.0 Preview fails with error: “0x8007138B, The cluster resource cannot be made dependent on the specified resource because it is already dependent.) I end-up using 2015 versions. Posted the bug at Microsoft connect with the nickname DonBox, now available here.
2. Build Solution via MSBuild
2.1. Build an extension task to let MSBuild build Visual Studio dtProj files.
After downloading the source code from CodePlex, here, you must install SSDT 2015 in your development machine.
Open the project and edit in text mode as you see in the following image, to set the path for the following 3 assemblies:
$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL
$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL
$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL
My system variable VS140COMNTOOLS point to C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\Tools\, SSDT 2015 installation updates Visual Studio private assemblies.
This source code defines a new MSBuild task, named DeploymentFileCompilerTask that generates a ispac file using project artefacts.
The downloaded project is marked as an strong-named assembly, it is not a requirement, but if you want to maintain the strong-name you must generate a new par of keys associated with the project.
Now invoke MSBuild using this fresh built extension, you can do this via cmd console using the following command after generating a xml parameter file.
MSBuild MSBuild_Script_For_SSIS.proj
The content of the file MSBuild_Script_For_SSIS.proj is the following:
<?xml version="1.0" encoding="Windows-1252"?> <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild"> <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:/Windows/Microsoft.NET/Framework/v4.0.30319/Microsoft.SqlServer.IntegrationServices.Build.dll" /> <Target Name="SSISBuild"> <DeploymentFileCompilerTask InputProject="projNameHere" Configuration="Development" ProtectionLevel="DontSaveSensitive"/> </Target> </Project>
In this file you specify the MSBuild task to use and the assembly were the task is located.
In my case I have copied the generated assembly and dependencies to C:/Windows/Microsoft.NET/Framework/v4.0.30319.
In my case the build process is managed by a Ruby/Rake application, I will leave here the code of the Ruby Task I use to make the build of the solutions for your analysis.
module Build
class CompileSSISTask < Task
attr_reader :solutions
def initialize(rake_name, build_spec, logger)
super(rake_name, build_spec, logger)
@solutions = []
end
def setup
@solutions = get_projects_to_compile
end
def execute
@solutions.each do |sln|
get_dtprj_from_solution(sln).each do |dtProj|
puts 'Solução: ' + sln
puts 'Projecto encontrado na solução: ' + dtProj
filepath = (File.dirname(dtProj)+"\\MSBuild_Script_For_SSIS.proj")
puts 'Ficheiro de extensão MSBuild: ' + filepath
xml = %q{<?xml version="1.0" encoding="Windows-1252"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild">
<UsingTask TaskName="DeploymentFileCompilerTask"
AssemblyFile="C:/Windows/Microsoft.NET/Framework/v4.0.30319/Microsoft.SqlServer.IntegrationServices.Build.dll" />
<Target Name="SSISBuild">
<DeploymentFileCompilerTask InputProject="}+dtProj+%q{" Configuration="Development" ProtectionLevel="DontSaveSensitive"></DeploymentFileCompilerTask>
</Target></Project>}
File.open(filepath, 'w') { |file| file.write(xml) }
sdk_path = get_sdk_path
puts 'SDK Path: ' + sdk_path
msbuild_path = get_msbuild_path
if(!msbuild_path.nil? && msbuild_path != "")
msbuild = "\"#{msbuild_path}/msbuild.exe\""
else
msbuild = "#{sdk_path}/msbuild"
end
command = "#{msbuild} #{filepath}"
puts command
exec_and_log(command)
end
end
end
private
def get_projects_to_compile
build_spec.get_spec_for(rake_name.to_sym, :projects_to_compile, Rake::FileList[File.join(build_spec.working_dir_path, '*VSSSIS*.sln')])
end
def get_dtprj_from_solution(slnfile)
references = []
dtproj = []
dirname = ""
File.open(slnfile, "r") do |f|
dirname = File.dirname f
f.each_line do |line|
if(line.start_with? "Project")
tokens = line.split(',')
candidate = tokens[1]
if(!candidate.nil?)
if(candidate.include? ".dtproj")
references.push candidate.gsub('"','').strip!
else
puts "\tskipping #{candidate}." if @verbose
end
end
end
end
end
references.each do |c|
c = File.join(dirname,c)
dtproj.push c
end
return dtproj
end
def get_msbuild_path
msbuild_version = build_spec.get_spec_for(rake_name.to_sym, :msbuild_version, "")
if(msbuild_version == "14" || msbuild_version == "14.0")
Build::MSBUILD_14_PATH
end
end
def get_sdk_path
dot_net_version = build_spec.get_spec_for(rake_name.to_sym, :dot_net_version, "default")
# if dot_net_version == "default"
# Build::DOT_NET_SDK_PATH
#elsif (dot_net_version == "2.0" || dot_net_version == "2")
# Build::DOT_NET_2_0_SDK_PATH
#elsif (dot_net_version == "4.0" || dot_net_version == "4")
Build::DOT_NET_4_0_SDK_PATH
#end
end
end
end
3. Deploy via Power Shell 4
Now that we have the ispac, let us deploy it in a automated process.
To execute the deploy we also used the assemblies in SSDT 2015, the language of choice is PowerShell.
I wrote three scripts, one to install the ispac in a Integration Server as is.
the second one lists all possible parameter parametrization in a SSIS project.
A third to set the parameter values using as a souce a XML data file.
3.1. PowerShell Deploy scripts
This script receives as parameter the path for the ispac file ($ProjectFilePath ), the SSIS project name, the SSIS connection string, the folder name and its description.
Param
(
[string]$ProjectFilePath = $(throw "ProjectFilePath is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required."), #required parameter,
[string]$FolderDescription = "" #required parameter
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"$scriptPathInclude = $scriptPath + "\log.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]$catalog = $integrationServices.Catalogs["SSISDB"]
$SSISfolder = $catalog.Folders[$FolderName]
LogMessage('Testing if folder '+ $FolderName +' exists!')
if (!$SSISfolder)
{
LogMessage ('Creating Folder ' + $FolderName +' in the SSIS Catalog')
$SSISfolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder ($catalog, $FolderName, $FolderDescription)
$SSISfolder.Create()
LogMessage($FolderName + ' Folder has been created in the SSIS Catalog')
}
LogMessage ('Deploying ' + $ProjectName + ' project ...')
# Read the project file, and deploy it to the folder
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$SSISfolder.DeployProject($ProjectName, $projectFile)
LogMessage($ProjectName + ' project has been deployed successfully')
Exit 0
}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)
#LogMessage ($_)
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue
exit 1;
}
3.2. List all existing parameters
This script lists all existing parameters in the project described in the parameters.
Param
(
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required.") #required parameter
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"$scriptPathInclude = $scriptPath + "\log.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{LogMessage("sqlConnectionString: "+$sqlConnectionString)
LogMessage("ProjectName: "+$ProjectName)
LogMessage("FolderName: "+$FolderName)# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]
$catalog = $integrationServices.Catalogs["SSISDB"]
$SSISfolder = $catalog.Folders[$FolderName]
$Project = $SSISfolder.Projects[$ProjectName]
Write-Host("Available parameters to set: ")
foreach($package in $Project.Packages)
{
Write-Host("Packages: " + $package.Name)
foreach($parameter in $package.Parameters)
{
Write-Host $Parameter.Name
}
}
Write-Host("Project: " + $Project.Name )
foreach ($Parameter in $Project.Parameters)
{
Write-Host $Parameter.Name
}
LogMessage($ProjectName + ' Parameters Listed.')
Exit 0
}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)
#LogMessage ($_)
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue
exit 1;
}
3.3. Set a parameter value
This last script allows to set parameter values, one of a time.
Param
(
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required."), #required parameter
[string]$name = $(throw "parameter name is required."), #required parameter
[string]$value = $(throw "parameter value is required."),
[string]$type
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"
$scriptPathInclude = $scriptPath + "\log.ps1"
$scriptPathPassword = $scriptPath + "\Get-Password.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{LogMessage("sqlConnectionString: "+$sqlConnectionString)
LogMessage("ProjectName: "+$ProjectName)
LogMessage("FolderName: "+$FolderName)
LogMessage("Parameters name: "+$name)
LogMessage("Parameters value: "+$value)
LogMessage("Parameters type: "+$type)# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]$catalog = $integrationServices.Catalogs["SSISDB"]$SSISfolder = $catalog.Folders[$FolderName]
$Project = $SSISfolder.Projects[$ProjectName]
#Write-Host("Name: " + $name)
#Write-Host("Value: " + $value)
Write-Host("Start probing the parameters: " + $name)
$found = $false
foreach($package in $Project.Packages)
{
foreach($parameter in $package.Parameters)
{
if($Parameter.Name.ToUpper() -eq $name.ToUpper())
{
if([string]::IsNullOrEmpty($type) -Or $type.ToUpper() -ne 'PASSWORD')
{
Write-Host "Setting parameter " $name " with value " $value
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$value)
$Project.Alter()
$found = $true
break;
}
else
{
Write-Host "Setting parameter " $name " with value " $value
$FilePassword = Invoke-Expression -Command ($scriptPathPassword + " -User " + $value)
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$FilePassword)
$Project.Alter()
$found = $true
break;
}
}
}
}
foreach ($Parameter in $Project.Parameters)
{
if($Parameter.Name.ToUpper() -eq $name.ToUpper())
{
if([string]::IsNullOrEmpty($type) -Or $type.ToUpper() -ne 'PASSWORD')
{
Write-Host "Setting parameter " $name " with value " $value
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$value)
$Project.Alter()
$found = $true
break;
}
else
{
Write-Host "Setting parameter " $name " with value " $value
$FilePassword = Invoke-Expression -Command ($scriptPathPassword + " -User " + $value)
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$FilePassword)
$Project.Alter()
$found = $true
break;
}
}
}
if($found -eq $false)
{
Write-Host "[WARNING]: Parameter " $name " not found!"
}
else
{
LogMessage($ProjectName + ' Parameter Configured successfully')
}
Exit 0
}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)
#LogMessage ($_)
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue
exit 1;
}
3.2 The PowerShell Orchestration
To execute the deployment I use a aplication in NANT version 0.92. This application receives as input a XML file with the indication of the location of the ispac files as theirs respective parameters values.
The input files look like this:
<ssisInstall>
<ssis projectFilePath="\bin\Development\ssis1.ispac"
projectName="MyProject" connectionString="Data Source=SQLSSIS;Initial
Catalog=master;Integrated Security=SSPI;" folderName="MyProject folderDescription="">
<parameters showparameters="true">
<parameter name="ServerName" value="ESA1DB2P">
<parameter name="ConnectionString" value="Data Source=SERVERc;User ID=USER;
Initial Catalog=ESA ;Provider=DB2OLEDB.1">
<parameter name="password" value="userAccount" type="password">
<parameters/>
</ssis>
<ssis projectFilePath="\bin\Development\ssis2.ispac"
projectName="MyProject2" connectionString="Data Source=SQLSSIS;Initial
Catalog=master;Integrated Security=SSPI;" folderName="MyProjectfolderDescription="">
<parameters showparameters="true">
<parameter name="CM.ServerName" value="Server2">
<parameters/>
</ssis>
...
</ssisInstall>
File specifications:
projectFilePath- file system path to *.ispac, relative path;
projectName- SSIS project name;
environment- Reserved for future user;
connectionString- Connection string for the server;
folderName- Name of the folder in SSIS catalog
folderDescription – Description associated to the folder;
showparameters – Indication if during deploy all possible parameters configurations must me printed
parameter name – name of the parameter
parameter value – value to assign to parameter
type- Optional, if password, this will be applied during execution time, translating the name of the user for the password.
The NANT task is the following:
<?xml version="1.0" ?>
<project>
<target name="ssisInstall">
<!-- Declare properties -->
<property name="ssisInstall.projectFilePath" value=""/>
<property name="ssisInstall.projectName" value=""/>
<property name="ssisInstall.environment" value=""/>
<property name="ssisInstall.connectionString" value=""/>
<property name="ssisInstall.folderName" value=""/>
<property name="ssisInstall.folderDescription" value=""/>
<property name="ssisInstall.parameter.value" value=""/>
<property name="ssisInstall.parameter.name" value=""/>
<property name="ssisInstall.parameter.type" value=""/>
<property name="ssisInstall.parameters.showparameters" value=""/>
<property name="ssisInstall.prefix" value="" />
<!-- Iterate over ssis instalations settings -->
<xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis">
<!-- Read instalation properties -->
<xmlpropertybinding>
<get xpath="@projectFilePath" property="ssisInstall.projectFilePath"/>
<get xpath="@projectName" property="ssisInstall.projectName"/>
<get xpath="@environment" property="ssisInstall.environment"/>
<get xpath="@connectionString" property="ssisInstall.connectionString"/>
<get xpath="@folderName" property="ssisInstall.folderName"/>
<get xpath="@folderDescription" property="ssisInstall.folderDescription"/>
<get xpath="@prefix" property="ssisInstall.prefix" />
</xmlpropertybinding>
<do>
<echo message="[project.prefixList]: ${project.prefixList}"></echo>
<echo message="[ssisInstall.prefix]: ${ssisInstall.prefix}"></echo>
<stringMatch stringCompare="${ssisInstall.prefix}" stringList="${project.prefixList}" returnProperty="ssisInstall.prefixMatch"></stringMatch>
<if test="${(string::to-upper(ssisInstall.prefixMatch) == string::to-upper(ssisInstall.prefix) or string::to-upper(project.prefixList) == 'ALL')}">
<!-- Check for mandatory values -->
<fail message="Property projectFilePath not found in manifest" if="${ssisInstall.projectFilePath == ''}" />
<fail message="Property projectName not found in manifest" if="${ssisInstall.projectName == ''}" />
<fail message="Property connectionString not found in manifest" if="${ssisInstall.connectionString == ''}" />
<fail message="Property folderName not found in manifest" if="${ssisInstall.folderName == ''}" />
<!-- Debug: print values -->
<echo message="${ssisInstall.projectFilePath}"/>
<echo message="${ssisInstall.projectName}"/>
<echo message="${ssisInstall.environment}"/>
<echo message="${ssisInstall.connectionString}"/>
<echo message="${ssisInstall.folderName}"/>
<echo message="${ssisInstall.folderDescription}"/>
<!-- Execute ISPAC instalation -->
<call target="ssisInstall.Execute"/>
<!-- Read Parameters properties-->
<xmlpeek
file="${ManifestFile}"
xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis/parameters/@showparameters"
property="ssisInstall.parameters.showparameters">
</xmlpeek>
<echo message="ssisInstall.parameters.showparameters: ${ssisInstall.parameters.showparameters}"/>
<if test="${bool::parse(ssisInstall.parameters.showparameters)}">
<call target="ssisInstall.Parameters.ShowAll"/>
</if>
<xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis/parameters/parameter">
<xmlpropertybinding>
<get xpath="@name" property="ssisInstall.parameter.name"/>
<get xpath="@value" property="ssisInstall.parameter.value"/>
<get xpath="@type" property="ssisInstall.parameter.type"/>
</xmlpropertybinding>
<do>
<call target="ssisInstall.Parameters.Execute"/>
<property name="ssisInstall.parameter.name" value=""/>
<property name="ssisInstall.parameters.value" value=""/>
</do>
</xml-foreach>
<property name="ssisInstall.projectFilePath" value=""/>
<property name="ssisInstall.projectName" value=""/>
<property name="ssisInstall.environment" value=""/>
<property name="ssisInstall.connectionString" value=""/>
<property name="ssisInstall.folderName" value=""/>
<property name="ssisInstall.folderDescription" value=""/>
</if>
</do>
</xml-foreach>
</target>
<target name="ssisInstall.Execute">
<property name="ssisInstall.args" value="-ProjectFilePath "${project.artifactsDir}/${ssisInstall.projectFilePath}" -ProjectName "${ssisInstall.projectName}" -EnvironmentName ${ssisInstall.environment} -sqlConnectionString "${ssisInstall.connectionString}" -FolderName "${ssisInstall.folderName}" -FolderDescription "${ssisInstall.folderDescription}"" />
<echo message="ssisInstall.args: ${ssisInstall.args}" />
<powershell4 version="4"
scriptfile="${deployCommon.powershell.path}\installIspac.ps1"
scriptargs="${ssisInstall.args}"
resultproperty="result"
timeout ="600000"
failonerror="true"
verbose="true"/>
</target>
<target name="ssisInstall.Parameters.ShowAll">
<property name="ssisInstall.Parameters.ShowAll.args" value="-sqlConnectionString "${ssisInstall.connectionString}" -ProjectName "${ssisInstall.projectName}" -FolderName "${ssisInstall.folderName}"" />
<echo message="ssisInstall.Parameters.ShowAll.Args: ${ssisInstall.Parameters.ShowAll.args}" />
<powershell4 version="4"
scriptfile="${deployCommon.powershell.path}\installIspacParametersShowAll.ps1"
scriptargs="${ssisInstall.Parameters.ShowAll.args}"
resultproperty="result"
timeout ="600000"
failonerror="true"
verbose="true"/>
</target>
<target name="ssisInstall.Parameters.Execute">
<property name="ssisInstall.Parameters.args" value="-sqlConnectionString "${ssisInstall.connectionString}" -ProjectName "${ssisInstall.projectName}" -FolderName "${ssisInstall.folderName}" -name "${ssisInstall.parameter.name}" -value "${ssisInstall.parameter.value}"-value "${ssisInstall.parameter.type}"" />
<echo message="ssisInstall.Parameters.Args: ${ssisInstall.Parameters.args}" />
<powershell4 version="4"
scriptfile="${deployCommon.powershell.path}\installIspacParameters.ps1"
scriptargs="${ssisInstall.Parameters.args}"
resultproperty="result"
timeout ="600000"
failonerror="true"
verbose="true"/>
</target>
</project>
Final notes:
This process allows the automation of builds and deploy of SSIS solutions. The orchestration of the Ruby\Rake and Nant application is made by Hudson Server.
King Regards,
Nuno Monteiro
One thought on “SQL Server Integration Services build and deploy automation”