Sunday, 17 November 2013

MSDTC in SSIS development

A colleague recently encountered a problem when developing a SSIS package in Visual Studio 2012 that uses transactions.  The package has an Execute SQL task with the TransactionOption set to ‘Required’, that calls a stored procedure on a SQL Server database located on one of the LAN servers.  Each time the package is run in Visual Studio it hangs for about 3 minutes and then fails with the following error:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E “The transaction has already been implicitly or explicitly committed or aborted”.

If transactions are disabled (or set to Supported) then the package runs fine, so it’s not a SQL Server or permissions issue.

I’ve used MSDTC for years with SQL Server and WCF and all sorts, so I was quite baffled by this.  Basic checks:

  1. Go into comexp.msc and navigate to Local DTC:

    msdtc

  2. Right-click Local DTC and select “Properties”.  Tick all check boxes on the security page apart from “Allow Remote Administration”:

    security

  3. Go to your Firewall (firewall.cpl) and select “Allow and app or feature through Windows Firewall” (in the left-hand sidebar):

    firewall

  4. Click on “Change settings” and under “Allowed apps and features” tick all the boxes for Distributed Transaction Coordinator:

    fwdtc

All these were set correctly and it still wasn’t working.  After much searching I found this page which contains a very useful diagnostic checklist.  One of the tools to use was DTCPing, which I downloaded and installed on my local machine and the SQL Server.  I also installed it on another SQL Server on the network.  Diagnostics showed that the 2 LAN SQL Servers could successfully coordinate a distributed transaction between themselves, but nothing between my PC and the network SQL Servers was working.

So I did the obvious thing: I pinged my PC from the SQL Servers and they couldn’t see it.  That’s because I was logged on via VPN and the VPN clients are on a different subnet to the servers.  Due to the network topology there are a whole bunch of servers that can’t see a whole bunch of clients (this is by design).  There was the problem!  I wasn’t (previously) aware that for MSDTC to work all parties in the transaction must be able to see each other.  One way communication (e.g. from behind a router or firewall that uses NAT) is insufficient.  Sure enough, I fudged DNS and static routes so that the server could see my PC, reran the package, and it all worked fine.

However, I didn’t want to allow servers to access VPN clients as a matter of course.  But in production the servers will all be able to see each other and so transactions must be enabled. As a workaround I’ve set the value of TransactionOption using an expression so that it’s always “Supported” in Debug mode and “Required” otherwise.  The only point to note with this is that if you are using an expression then it must evaluate to the underlying enumeration’s value (as helpfully pointed out here).  Personally I used a boolean project parameter (as they can be set for each Visual Studio configuration) called “DisableTransactions”, so my expression for TransactionOption looks like this:

@[$Project::DisableTransactions] ? 1 : 2

Also worth noting that the same rules (about all parties seeing each other) apply to WS-AT in WCF (in fact there’s a whole load of prerequisites that make distributed transactions over HTTP quite onerous).

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"
    }
}