Friday, 8 November 2013

SSIS project deployment and Visual Studio configurations

One of the great improvements made to SSIS in SQL Server 2012 was the introduction of the SSIS catalog and, along with it, project environments.  Environments allow you to apply a different set of stored configurations to the same packages – which can be very useful in testing and production scenarios.  Unfortunately Visual Studio/SSDT doesn’t quite have the level of deployment support that one would expect.  The obvious (and most frustrating) issues are:
1. Deployment locations are stored in the project user settings file (.dtProj.User) which means that they are user and machine specific.  The file doesn’t get (and shouldn’t be) source controlled so it’s difficult to apply consistent deployment settings across a team.
2. Project parameters applied to specific Visual Studio configurations are completely ignored in deployments.
3. There is no facility in the deploy to create environments in the catalog.
Out of the box, these issues can’t be gotten around using msbuild, because msbuild does not natively support SSIS project compilation or deployment.  However there is an example of an msbuild task for SSIS in the community samples on CodePlex (it’s under source code->main->SSISMSBuild->Project) which is great (and there’s a good article here explaining how to use it).  However if, like me, you don’t want the overhead of having to deploy another .dll just so you can build SSIS projects, then you can use PowerShell to achieve the same thing.
What I’ve done is create a PowerShell version of the msbuild task to create the .ispac file and then cannibalised Matt Mason’s excellent script to do the actual deployment.  Given the path to a .dtProj file, the complete script does the following:
  1. Compiles the SSIS project to an .ispac
  2. Deploys to a catalog
  3. Creates an environment in the catalog according to the specified Visual Studio configuration
You need to have created your per-configuration project parameters by clicking on the “Add Parameters to Configurations” icon in the Project Parameters window:
paramconfig
Otherwise the parameter values won’t get picked up in the deployed environment.  You also need to have created your SSIS catalog as I haven’t included this step in the PowerShell (although you can easily pick it up from Matt Mason’s script).
To use the script, save it as SSIS-CatalogDeploy.ps1 and drop it into your SSIS project folder (i.e. the same folder where the .dtProj file is) and create a batch file for each Visual Studio configuration (which is also placed in the SSIS project folder).  e.g. For the Release configuration of a project file called MyETL.dtProj to be deployed to the MyDW folder of the SSIS catalog on a server called MyProdSQLServer, you would create the following batch script:
powershell.exe -ExecutionPolicy Unrestricted -File .\SSIS-CatalogDeploy.ps1 -projectFile %CD%\MyETL.dtProj -configuration "Release" -ssisServer MyProdSQLServer -deployPath "/SSISDB/MyDW"
By default, the environment that gets created is given the same name as the configuration.  I find it quite useful to create an environment per-developer for testing, so you can set the environment name using the environmentName switch.  Also, by default existing variable values are not overwritten – to do this use the overwriteVariables switch.  For example, the above batch script could be rewritten to overwrite values and create an environment named after the deploying user:
powershell.exe -ExecutionPolicy Unrestricted -File .\SSIS-CatalogDeploy.ps1 -projectFile %CD%\MyETL.dtProj -configuration "Release" -ssisServer MyProdSQLServer -deployPath "/SSISDB/MyDW" -environmentName %USERNAME% -overwriteVariables
Every time you want to deploy, just run the appropriate batch script (which can be added to the project’s miscellaneous items and source controlled).
The script appears below:
param([string]$projectFile, [string]$configuration, [string]$ssisServer, [string]$deployPath, [string]$environmentName, [switch]$overwriteVariables, [string]$projectPassword)

if (!$projectFile -Or !$configuration -Or !$ssisServer -Or !$deployPath)
{
    ([string]$MyInvocation.MyCommand.Name) + " [-projectFile <string>] [-configuration <string>] [-ssisServer <string>] [-deployPath <string>] [-environmentName <string>] [-overwriteVariables] [-projectPassword <string>]"
    return
}

$projectPath = [System.IO.Path]::GetDirectoryName($projectFile)

if (!$projectPath)
{
    $projectPath = $MyInvocation.MyCommand.Path
}

$projectFileName = [System.IO.Path]::GetFileName($projectFile)
$projectName = [System.IO.Path]::GetFileNameWithoutExtension($projectFile)

$pathOnServer = $deployPath

if (!$deployPath.EndsWith("/"))
{
    $pathOnServer = $pathOnServer + "/"
}

$pathOnServer = $pathOnServer + $projectName

Add-Type @'
public class SSISProperties
{
    public string Name {get; set;}
    public string XPath {get; set;}
    public string Value {get; set;}

    public SSISProperties(string name, string xPath) 
    {
        this.Name = name;
        this.XPath = xPath;
    }
}
'@

Add-Type @'
public class SSISParameter
{
    public string Name {get; set;}
    public System.Collections.Generic.Dictionary<string, string> Properties {get; set;}

    public SSISParameter(string name) 
    {
        this.Name = name;
        this.Properties = new System.Collections.Generic.Dictionary<string, string>();
    }
}
'@

if (!$environmentName)
{
    $environmentName = $configuration
}

$ispacFileName = $projectName + ".ispac"
$ispacFullPath = [System.IO.Path]::Combine($projectPath, "bin", $configuration)

if (![IO.Directory]::Exists($ispacFullPath))
{
    [IO.Directory]::CreateDirectory($ispacFullPath)
}

$ispacFullPath = [System.IO.Path]::Combine($ispacFullPath, $ispacFileName)

$projectFullPath = [System.IO.Path]::Combine($projectPath, $projectFileName)

$namespace = @{ 
    SSIS = "www.microsoft.com/SqlServer/SSIS"
    DTS = "www.microsoft.com/SqlServer/Dts" }
$projectXml = [xml](Get-Content $projectFullPath)
$deploymentModel = Select-Xml -Xml $projectXml -XPath "//Project/DeploymentModel" | Select -ExpandProperty Node | Select -ExpandProperty "#text"

if ($deploymentModel -eq "Project")
{    
    $protectionLevelXPath = "//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project[@SSIS:ProtectionLevel]"
    $protectionLevel = Select-Xml -Xml $projectXml -XPath $protectionLevelXPath -Namespace $namespace | Select -ExpandProperty Node | Select -ExpandProperty "ProtectionLevel"

    if ($protectionLevel.Contains("Password") -And !$projectPassword)
    {
        "Protection level of $protectionLevel requires a password"
        return
    }

    $projectAttributes = @()
    $projectAttributes = $projectAttributes + (New-Object SSISProperties("MinorVersion","//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Properties/SSIS:Property[@SSIS:Name='VersionMinor']"))
    $projectAttributes = $projectAttributes + (New-Object SSISProperties("MajorVersion","//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Properties/SSIS:Property[@SSIS:Name='VersionMajor']"))
    $projectAttributes = $projectAttributes + (New-Object SSISProperties("BuildVersion","//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Properties/SSIS:Property[@SSIS:Name='VersionBuild']"))
    $projectAttributes = $projectAttributes + (New-Object SSISProperties("Description","//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Properties/SSIS:Property[@SSIS:Name='Description']"))
    $projectAttributes = $projectAttributes + (New-Object SSISProperties("Comments","//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Properties/SSIS:Property[@SSIS:Name='VersionComments']"))

    foreach ($path in $projectAttributes)
    {        
        $node = Select-Xml -Xml $projectXml -XPath $path.XPath -Namespace $namespace  | Select -ExpandProperty Node

        if ($node."#text")
        {
            $path.Value = $node | Select -ExpandProperty "#text"
        }
    }

    $loadStatus = [System.Reflection.Assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

    "Creating project $ispacFullPath..."
    $project = [Microsoft.SqlServer.Dts.Runtime.Project]::CreateProject($ispacFullPath)

    "Setting project attributes..."
    $project.Name = $projectName
    $project.VersionMinor = $projectAttributes | Where-Object { $_.Name -eq "MinorVersion" } | %{ [int]$_.Value }
    $project.VersionMajor = $projectAttributes | Where-Object { $_.Name -eq "MajorVersion" } | %{ [int]$_.Value }
    $project.VersionBuild = $projectAttributes | Where-Object { $_.Name -eq "BuildVersion" } | %{ [int]$_.Value }
    $project.Description = $projectAttributes | Where-Object { $_.Name -eq "Description" } | %{ $_.Value }
    $project.VersionComments = $projectAttributes | Where-Object { $_.Name -eq "Comments" } | %{ $_.Value }
    $project.ProtectionLevel = $protectionLevel    

    if ($protectionLevel.Contains("Password"))
    {
        $project.Password = $projectPassword
    }

    "Retrieving project parameters..."
    $parameterPath = [System.IO.Path]::Combine($projectPath, "Project.Params")
    $parameterXml = [xml](Get-Content $parameterPath)
    $parameterNodes = Select-Xml -Xml $parameterXml -XPath "//SSIS:Parameters" -Namespace $namespace | Select -ExpandProperty Node
    $SSISparameters = @()    
    $defaultEvents = (New-Object Microsoft.SqlServer.Dts.Runtime.DefaultEvents)

    foreach ($parameterNode in $parameterNodes.ChildNodes)
    {
        $SSISparameter = New-Object SSISParameter($parameterNode."Name")        

        if ($parameterNode.ChildNodes.Count -gt 0)
        {
            foreach ($propertyNode in $parameterNode.ChildNodes[0].ChildNodes)
            {
                $SSISparameter.Properties.Add($propertyNode."Name", $propertyNode."#text")
            }
        }

        $SSISparameters = $SSISparameters + $SSISparameter                
        $parameter = $project.Parameters.Add($SSISparameter.Name, [System.TypeCode]([int]$SSISparameter.Properties["DataType"]));        
        $parameter.LoadFromXML($parameterNode, $defaultEvents)
        "Parameter '" + $SSISparameter.Name + "' added"
    }

    "Retrieving connection managers..."    
    $connectionManagerNames = Select-Xml -Xml $projectXml -XPath "//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:ConnectionManagers" -Namespace $namespace | Select -ExpandProperty Node

    foreach ($connectionManagerName in $connectionManagerNames.ChildNodes)
    {
        $cmPath = [System.IO.Path]::Combine($projectPath, $connectionManagerName."Name")
        $cmXml = [xml](Get-Content $cmPath)
        $cmNode = Select-Xml -Xml $cmXml -XPath "//DTS:ConnectionManager" -Namespace $namespace | Select -ExpandProperty Node

        if ($cmNode."CreationName")
        {
            $creationName = ([System.String]$cmNode."CreationName").Trim()
            $connectionManager = $project.ConnectionManagerItems.Add($creationName, $connectionManagerName."Name")
            $connectionManager.Load($null, [System.IO.File]::OpenRead($cmPath))
            "Adding connection manager " + $connectionManagerName."Name"
        }
    }

    "Retrieving packages..."
    $packageNames = Select-Xml -Xml $projectXml -XPath "//Project/DeploymentModelSpecificContent/Manifest/SSIS:Project/SSIS:Packages" -Namespace $namespace | Select -ExpandProperty Node

    foreach ($packageName in $packageNames.ChildNodes)
    {
        $packagePath = [System.IO.Path]::Combine($projectPath, $packageName."Name")                
        $packageXml = [System.IO.File]::ReadAllText($packagePath)
        $package = New-Object Microsoft.SqlServer.Dts.Runtime.Package
        $package.LoadFromXML($packageXml, $null)

        if ($package.ProtectionLevel -ne $protectionLevel)
        {
            $package.ProtectionLevel = $protectionLevel

            if ($protectionLevel.Contains("Password"))
            {
                $package.PackagePassword = $projectPassword
            }
        }

        $project.PackageItems.Add($package, $packageName."Name")
        $project.PackageItems[$packageName."Name"].EntryPoint = $packageName."EntryPoint"
        "Adding package " + $packageName."Name"
    }

    $project.Save()
    $project.Dispose()
    "$ispacFullPath created..."

    "Retrieving deployment location..."
    $elements = $deployPath.Split('/')

    if ($elements.Count -gt 2)
    {
        $catalogName = $elements[1]
        $folderName = $elements[2]
        $configurationNodes = $projectXml.SelectNodes("//Project/Configurations/Configuration[Name='$configuration']/Options/ParameterConfigurationValues/ConfigurationSetting[Name[starts-with(.,'Project')]]")

        "Connecting to $ssisServer..."
        $loadStatus = [Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
        $constr = "Data Source=$ssisServer;Initial Catalog=master;Integrated Security=SSPI;"
        $con = New-Object System.Data.SqlClient.SqlConnection $constr
        $ssis = New-Object "Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices" $con
        $catalog = $ssis.Catalogs[$catalogName]

        if ($catalog)
        {
            $folder = $catalog.Folders[$folderName]

            if (!$folder)
            {
                $folder = New-Object "Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder" ($catalog, $folderName, $null)
                $folder.Create()
                "Folder $folderName created..."
            }

            [byte[]] $ispacFile = [System.IO.File]::ReadAllBytes($ispacFullPath)
            $deployStatus = $folder.DeployProject($projectName, $ispacFile)
            "Project $projectName deployed..."

            $environment = $folder.Environments[$environmentName]
            
            if ($environment -eq $null) 
            {
                $environment = New-Object "Microsoft.SqlServer.Management.IntegrationServices.EnvironmentInfo" ($folder, $environmentName, "")
                $environment.Create()
                "Created environment '$environmentName'"
            }
            else
            {
                "Environment '$environmentName' already exists"
            }

            $project = $folder.Projects[$projectName]

            foreach ($configurationNode in $configurationNodes) 
            {
                $typeCode = [System.TypeCode]::String

                switch ($configurationNode.Value."type")
                {
                    "xsd:int" { $typeCode = [System.TypeCode]::Int32 }
                    "xsd:boolean" { $typeCode = [System.TypeCode]::Boolean }
                    "xsd:unsignedByte" { $typeCode = [System.TypeCode]::Byte }
                    "xsd:datetime" { $typeCode = [System.TypeCode]::DateTime }
                    "xsd:decimal" { $typeCode = [System.TypeCode]::Decimal }
                    "xsd:double" { $typeCode = [System.TypeCode]::Double }
                    "xsd:short" { $typeCode = [System.TypeCode]::Int16 }
                    "xsd:long" { $typeCode = [System.TypeCode]::Int64 }
                    "xsd:byte" { $typeCode = [System.TypeCode]::SByte }
                    "xsd:float" { $typeCode = [System.TypeCode]::Single }
                    "xsd:unsignedint" { $typeCode = [System.TypeCode]::UInt32 }
                    "xsd:unsignedlong" { $typeCode = [System.TypeCode]::UInt64 }
                }

                if ($configurationNode.Value."#text")
                {
                    $variableName = $configurationNode.Name.Replace("Project::","")

                    if ($environment.Variables[$variableName] -And $overwriteVariables)
                    {
                        $environment.Variables.Remove($variableName);
                        $environment.Alter()
                    }

                    if (!$environment.Variables[$variableName])
                    {
                        $environment.Variables.Add($variableName, $typeCode, $configurationNode.Value."#text","","")
                        $environment.Alter()
                        "Added and set variable '$variableName' to '" + $configurationNode.Value."#text" + "' in environment '$environmentName'"
                    }

                    $projectVariable = $project.Parameters[$variableName]
                    if ($projectVariable)
                    {                            
                        $projectVariable.Set("Referenced",$variableName)
                        $project.Alter()                        
                    }                    
                }
            }
                
            $referenceExists = $false
            foreach ($reference in $project.References)
            {
                if ($reference.Name -eq $environmentName)
                {
                    $referenceExists = $true
                }
            }

            if (!$referenceExists)
            {
                $project.References.Add($environmentName, $folderName)
                $project.Alter()
                "Added reference for environment '$environmentName'"
            }
        }
        else
        {
            "The catalog '$catalogName' does not exist"
        }
    }
    else
    {
        "The deployment path '$deployPath' is invalid.  It should be in the format '/<catalog name>/<folder name>'"
        "e.g. /SSISDB/MyFolder"
    }
}

1 comment:

  1. Very nice! Just what I needed to deploy my packages to the SSISDB.
    Reading the script makes the MOM a little bit more comprehensive.

    Thanks!

    Blaise.

    ReplyDelete