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

Tuesday, 17 September 2013

Specifying the timeout for SSIS 2012 package execution

This is a follow up to my post from last year about executing packages in the SSIS catalog programmatically.  The main problem that makes it unworkable for production scenarios is that packages cannot be run synchronously if they take longer than 30 seconds.  The reason for this is probably because the Microsoft.SqlServer.Management.IntegrationServices.dll assembly is simply using a SqlCommand object, which has a default timeout of 30 seconds, to call the SSIS catalog stored procedures.  Unfortunately the assembly doesn’t provide any way to get at the timeout property.

Unlike the assembly, the underlying stored procedures are fairly well documented.  So it’s not a huge leap to create your own wrapper to call the execute stored procedure, but with a way of changing the timeout value.  The least intrusive way to do this is to use extension methods to provide alternative versions of the Execute methods that allow synchronous execution:

public static class Extensions
{
    #region Private constants

    private const string CREATE_EXECUTION = @"[catalog].[create_execution]";
    private const string SET_EXECUTION_PARAMETER_VALUE = @"[catalog].[set_execution_parameter_value]";
    private const string SET_EXECUTION_PROPERTY_OVERRIDE_VALUE = @"[catalog].[set_execution_property_override_value]";
    private const string START_EXECUTION = @"[catalog].[start_execution]";

    private const string EXECUTION_ID = @"@execution_id";
    private const string OBJECT_TYPE = @"@object_type";
    private const string PARAMETER_NAME = @"@parameter_name";
    private const string PARAMETER_VALUE = @"@parameter_value";
    private const string PACKAGE_NAME = @"@package_name";
    private const string FOLDER_NAME = @"@folder_name";
    private const string PROJECT_NAME = @"@project_name";
    private const string USE32BITRUNTIME = @"@use32bitruntime";
    private const string REFERENCE_ID = @"@reference_id";
    private const string PROPERTY_PATH = @"@property_path";
    private const string PROPERTY_VALUE = @"@property_value";
    private const string SENSITIVE = @"@sensitive";

    #endregion

    #region Public extension methods

    public static long Execute(this PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        int commandTimeout)
    {
        return packageInfo.Execute(use32RuntimeOn64, reference, setValueParameters, null, commandTimeout);
    }

    public static long Execute(this PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        Collection<PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
        int commandTimeout)
    {
        long executionId = 0;
        string connectionString = packageInfo.Parent.Parent.Parent.Parent.Connection.ServerConnection.ConnectionString;

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            executionId = CreateExecution(packageInfo, use32RuntimeOn64,
                reference, commandTimeout, connection);

            SetExecutionParameterValues(packageInfo, setValueParameters,
                commandTimeout, connection, executionId);

            SetPropertyOverrideParameters(packageInfo, propertyOverrideParameters,
                commandTimeout, connection, executionId);

            StartExecution(commandTimeout, executionId, connection, packageInfo);
        }

        return executionId;
    }

    #endregion

    #region Private methods

    private static void StartExecution(int commandTimeout,
        long executionId, SqlConnection connection, PackageInfo packageInfo)
    {
        string storedProcName = string.Format("{0}.{1}",
        packageInfo.Parent.Parent.Parent.Name, START_EXECUTION);

        using (var command = new SqlCommand(storedProcName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = commandTimeout;

            command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
            command.Parameters[EXECUTION_ID].Value = executionId;
            command.ExecuteNonQuery();
        }
    }

    private static long CreateExecution(PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        int commandTimeout, SqlConnection connection)
    {
        long executionId = 0;
        string storedProcName = string.Format("{0}.{1}",
            packageInfo.Parent.Parent.Parent.Name, CREATE_EXECUTION);

        using (var command = new SqlCommand(storedProcName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = commandTimeout;

            command.Parameters.Add(FOLDER_NAME, SqlDbType.NVarChar, 128);
            command.Parameters.Add(PROJECT_NAME, SqlDbType.NVarChar, 128);
            command.Parameters.Add(PACKAGE_NAME, SqlDbType.NVarChar, 260);
            command.Parameters.Add(REFERENCE_ID, SqlDbType.BigInt);
            command.Parameters.Add(USE32BITRUNTIME, SqlDbType.Bit);
            command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
            command.Parameters[EXECUTION_ID].Direction = ParameterDirection.Output;

            command.Parameters[FOLDER_NAME].Value = packageInfo.Parent.Parent.Name;
            command.Parameters[PROJECT_NAME].Value = packageInfo.Parent.Name;
            command.Parameters[PACKAGE_NAME].Value = packageInfo.Name;

            if (reference != null)
            {
                command.Parameters[REFERENCE_ID].Value = reference.ReferenceId;
            }

            command.Parameters[USE32BITRUNTIME].Value = use32RuntimeOn64;

            command.ExecuteNonQuery();
            executionId = long.Parse(command.Parameters[EXECUTION_ID].Value.ToString());
        }

        return executionId;
    }

    private static void SetExecutionParameterValues(PackageInfo packageInfo,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        int commandTimeout, SqlConnection connection, long executionId)
    {
        if (setValueParameters != null && setValueParameters.Count > 0)
        {
            string storedProcName = string.Format("{0}.{1}",
                packageInfo.Parent.Parent.Parent.Name, SET_EXECUTION_PARAMETER_VALUE);

            using (var command = new SqlCommand(storedProcName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = commandTimeout;

                command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
                command.Parameters.Add(OBJECT_TYPE, SqlDbType.SmallInt);
                command.Parameters.Add(PARAMETER_NAME, SqlDbType.NVarChar, 128);
                command.Parameters.Add(PARAMETER_VALUE, SqlDbType.Variant);
                command.Parameters[EXECUTION_ID].Value = executionId;

                foreach (var setValueParameter in setValueParameters)
                {
                    command.Parameters[OBJECT_TYPE].Value = setValueParameter.ObjectType;
                    command.Parameters[PARAMETER_NAME].Value = setValueParameter.ParameterName;
                    command.Parameters[PARAMETER_VALUE].Value = setValueParameter.ParameterValue;
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    private static void SetPropertyOverrideParameters(PackageInfo packageInfo,
        Collection<PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
        int commandTimeout, SqlConnection connection, long executionId)
    {
        if (propertyOverrideParameters != null && propertyOverrideParameters.Count > 0)
        {
            string storedProcName = string.Format("{0}.{1}",
                packageInfo.Parent.Parent.Parent.Name, SET_EXECUTION_PROPERTY_OVERRIDE_VALUE);

            using (var command = new SqlCommand(storedProcName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = commandTimeout;

                command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
                command.Parameters.Add(PROPERTY_PATH, SqlDbType.NVarChar, 4000);
                command.Parameters.Add(PROPERTY_VALUE, SqlDbType.NVarChar, -1);
                command.Parameters.Add(SENSITIVE, SqlDbType.Bit);
                command.Parameters[EXECUTION_ID].Value = executionId;

                foreach (var propertyOverrideParameter in propertyOverrideParameters)
                {
                    command.Parameters[PROPERTY_PATH].Value = propertyOverrideParameter.PropertyPath;
                    command.Parameters[PROPERTY_VALUE].Value = propertyOverrideParameter.PropertyValue;
                    command.Parameters[SENSITIVE].Value = propertyOverrideParameter.Sensitive;
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    #endregion
}

Add the above class to your project (you can rename it to whatever you want) and now you can make the same call as before but you’ll be able to specify the timeout:

int timeOut = 60;
long executionIdentifier = package.Execute(false, null, setValueParameters, timeOut);

As with a normal SqlCommand execution, if you want no timeout then just supply a value of 0.

Friday, 12 July 2013

ASP.NET Universal Providers role management "feature"

Many moons ago I wrote a WPF application for managing the ASP.NET provider store.  I need a credential manager for the test team on a project I'm currently working on that uses the "new" Universal Providers.  So I assumed I could just dust off the old code, change the config a little and life would be good.
As with all good intentions, it didn't take me long to get to hell.  Specifically the following methods kept failing with some meaningless guff about object reference not being set to an instance etc.:
  • Roles.GetRolesForUser
  • Roles.IsUserInRole
I ran a SQL Profiler trace to make sure it wasn't trying to use the wrong provider, but it was obvious that it wasn't even hitting the data store.  The Roles object is static, so the method pointers should never be null.  This was obviously an issue with the provider.
My original code was:
var roles = Roles.GetRolesForUser(userName);
To cut a long story short, I called the method on the Provider property of the Roles object like so:
var provider = Roles.Provider;
Debug.Assert(provider != null);
var roles = provider.GetRolesForUser(userName);
Now all is well again.

Monday, 20 May 2013

Poor performance when upgrading SSRS reports from 2005

I recently had to upgrade some SSRS reports from 2005 to 2012.  I installed the SSDT/BIDS upgrade for VS2012 and imported them straight in.  I did come across the following issues:

But otherwise it all went pretty smoothly.  However after a while we started to get feedback about reports either loading very slowly or, in some cases, crashing/freezing the browser completely.  I tested the offending reports and although they did cause a freeze of a few seconds in IE9, IE10, and Chrome on Windows 7 64-bit – they did eventually render.  However they caused Firefox to display an error message:

A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.

Which prevented the report ever rendering and could only be made to go away by ending the process with Task Manager.  Also, running the report in VS2012 took over 2 minutes, whereas in VS2005 it only took a few seconds.  Exporting the report to a (MHTML) web archive did yield a slight difference in output size (the 2012 version was about 20% larger), but not enough to warrant the problems I was experiencing.  What was interesting was that exporting to Excel from either version (using the rs web service) was about 12-15 times faster than viewing in the browser.

What I eventually did (i.e. should have done in the first place) was to run a network capture with IE10 developer tools and identify the point of the freeze.  It was happening immediately after a call that was returning the body of the report – about 17MB of data.  My Internet connection is pretty fast so the download was only taking about 2 seconds, the freeze was happening after the download.  Understanding why took me back to the difference between SSRS 2005 and later versions.

Basically SSRS 2005 pre-rendered the whole report in cache before displaying.  So if you have a 100 page report then it would take as long to view it even if you only looked at 1 page.  SSRS 2008 and later versions only pre-rendered the visible page, thus placing less load on the server and being much more scalable.  So the fact that on a large report (1000 rows of data), SSRS 2005 was outperforming 2012 seemed counterintuitive.  What I failed to realise is that the problem was with the Report Viewer control.

The first versions of the Report Viewer displayed the report in an IFRAME – so it loads in the same way as every other web page, via a plain old HTTP request.  This is good when you want more data, less often – the whole page loads in one go.  The 2010 viewer uses Ajax (better for less data, more often), which means the whole body of the request has to be loaded into the JavaScript pipeline before it gets displayed in the browser.  Ajax isn’t intended for shoving large blocks of data around – it’s designed to break the process of loading a web page into easily manageable chunks.  So for multi-page reports it performs much better as the amount of content displayed at once is much less and it doesn’t have to reload the whole page.  However, 17MB on an Ajax request is a bit much (and very bad practice).  Too much, it would seem, for Firefox.  Certainly too much for older versions of IE that don’t use the Chakra engine.

So the solution is to introduce paging.  There are two ways of doing paging in SSRS:

  • Add page breaks
  • Set InteractiveHeight to a non-zero value

The difference is that page breaks will apply in all rendering extensions.  So a report with page breaks exported to Excel will put each page on a different worksheet.  Likewise exported to PDF it will put each page on a different page of the PDF (and often throw in some unwanted pages for good measure).

InteractiveHeight is what’s known as a soft page break.  It only appears in rendering extensions that are interactive (i.e. HTML in the browser).  For other extensions (e.g. Excel) it will render a single long report.  You should experiment to get the right size, both in terms of visuals and response size.  By cutting down my report to 20 items a page I reduced the response to 0.6MB and the rendering to 1 second.

Incidentally, multi-page reports don’t preclude you being able to do a search for words across the whole (i.e. visible and non-visible pages) of the report.  Using the find text box on the Report Viewer toolbar (as opposed to the browser’s search on page feature) will search the entire report.

I realise that this isn’t a solution as much as a workaround.  If you have money for bandwidth coming out of your ears and really must allow users to download 17MB web pages then some kind of hack that involves exporting to MHTML or otherwise being very clever with IFRAMEs and the reporting service web service, may be your only solution.

See also:

http://connect.microsoft.com/SQLServer/feedback/details/651339/ssrs-interactiveheight-0-and-ie-script-performance

http://www.bidn.com/blogs/MMilligan/bidn-blog/2903/ssrs-gotcha-interactiveheight=0

Wednesday, 17 April 2013

2012 Report Viewer, wait animation, and the double scroll bar

I’ve had to convert a load of reports from SSRS 2005 to 2012.  A few quirks notwithstanding, it’s mostly gone well.  However I was also tasked with getting rid of the dreaded double-scrollbar.  This was quite easy to do – just set the height of the report viewer control to 100% (I’m using the 2012 report viewer).  The problem is that when you do this the wait animation repositions itself to the top of the page, because until the report is rendered it’s also the bottom of the page.  Anyway this is fairly easy to get round with a spot of jQuery.
First, find the name of the wait animation div – the id is derived from the name of the control and suffixed with _AsyncWait_Wait (e.g. a control called myReportViewer would result in a wait div called myReportViewer_AsyncWait_Wait).  Add a reference to jQuery to your page, then add the following code in an inline JavaScript block (replacing the waitControl selector with the correct id for your div):
$(document).ready(function() {
    var waitControl = $("#myReportViewer_AsyncWait_Wait");

    if (waitControl.size() !== 0) {
        waitControl.css('margin-top', '300px');
    }
});
Not the neatest approach but it works.

UPDATE: After a good night's sleep I realised that a better approach is to use CSS (as this continues to apply even after page refreshes when Report Viewer rewrites its own DOM):

#myReportViewer_AsyncWait_Wait { margin-top:300px; }

Sunday, 3 March 2013

Deploying a Reporting Services project from the command line using the rs utility

I’m currently doing some work on a legacy project which involves a large amount of SQL Server Reporting Services assets.  As part of a migration I’m having to deploy a large number of Reporting (Visual Studio) projects.  Rather than have to open each one up in Visual Studio every time I want to do a full deployment, I thought it would be nice if I could just put everything in a root folder, point a script at said folder, and have said script automagically deploy everything to the right place based on a named configuration (e.g. Debug, Release, etc) in the .rptproj files.
There are many variations on this solution already out there, including one that uses msbuild, but my main aim was to get a whole bunch of projects quickly deployed with the configurations that have already been set in the Visual Studio project files.  So I’ve put together a script which pretty much does that.
It uses the rs utility, which is basically a wrapper around the SSRS web service and the vbc compiler.  All the rs utility does is take a VB.NET script, add a proxy variable for the SSRS web service which (oddly enough) it calls ‘rs’ and then create variables for various command line arguments that are passed in.
If you want to use Visual Studio for creating your rs scripts, then just create a VB.NET console application, add a web reference to the SSRS web service (typically located at http://localhost/reportserver/reportexecution2005.asmx for a local default instance install), then declare a module level variable like so:
Private rs As New ReportingService2005
Then you can just code away in the Main() Sub as normal.  Any other variables that you intend to pass in as command line parameters should also be declared as module level variables during development.  This allows you to compile and test your script before trying it out with the rs utility.  Once you’re happy with the script, copy it into a text file with an .rss extension, then remove all module level declarations so you’re left with just the Sub Main() and any other methods you’ve written.
One caveat is that you can’t (as far as I can work out) use the Imports statement in an .rss file.  Although some standard Imports are included by default (e.g. System.IO), if in doubt always use fully-namespaced type names.
If all else fails just hack my script!
    ' GenerateReportsFromDataset.rss
    '
    ' An rs script to automatically generate reports using parameter values based on an input dataset from a delimited text file.
    '
    ' The input parameters should be in a comma-delimited file with column headers in the first line 
    ' (these must match the Reporting Services parameter names)
    '
    ' A note on rs default arguments
    ' ====================
    ' 
    ' Obviously the input file (-i) and server name (-s) are mandatory and will be this file and the server on which the report is based, respectively.
    ' The -e parameter is also mandatory, but MUST ALWAYS HAVE A VALUE OF "Exec2005"
    '
    ' Arguments (-v parameters)
    ' =========================
    '
    ' reportPath:           The full path to the report on the reporting server (the leading slash can be left out)
    ' parameterSourcePath:  The full path of the file which contains the input parameters
    ' outputPath:           The path to which the generated reports should be output
    ' format:               The rendering format (e.g. HTML, PDF, etc.)
    ' filenameFormat:       A .NET format string which will be used to generate the output filename for any given report.
    '                       Each item in the format string corresponds to a column in the input file.
    '                       e.g.    a format string of "{0}-{3}" would produce an output file with a name consisting of the values in
    '                               columns 0 and 3 for the current row in the input dataset
    '
    '                       If this argument is not provided or left empty then all files will be given an auto-generated incrementing numerical value
    '
    ' Usage example:
    '
    ' rs -i GenerateReportsFromDataset.rss -s http://localhost/reportserver -e Exec2005 -v reportPath="MyReport" 
    '       -v parameterSourcePath="C:\MyParameterFile.txt" -v outputPath="C:\MyReports" -v format="PDF" -v filenameFormat="{1}-{4}"
    '

    Sub Main()

        rs.Credentials = System.Net.CredentialCache.DefaultCredentials

        If Not reportPath.StartsWith("/") Then reportPath = "/" & reportPath

        Dim result() As Byte = Nothing
        Dim historyID As String = Nothing
        Dim devInfo As String = "False"

        Using parameterStream As New System.IO.FileStream(parameterSourcePath, IO.FileMode.Open, IO.FileAccess.Read)
            Using parameterReader As New System.IO.StreamReader(parameterStream)

                Dim isFirstLine As Boolean = True
                Dim reportCount As Long = 0

                While Not parameterReader.EndOfStream

                    Dim currentLine As String = parameterReader.ReadLine()
                    Dim parameters() As ParameterValue

                    If Not String.IsNullOrEmpty(currentLine) Then

                        Dim parameterColumns() As String = currentLine.Split(New Char() {","c})

                        For loopCounter As Integer = 0 To (parameterColumns.Length - 1)
                            parameterColumns(loopCounter) = RemoveLeadingAndTrailingQuotes(parameterColumns(loopCounter))
                        Next loopCounter

                        If isFirstLine Then

                            If parameterColumns.Length > 0 Then
                                ReDim parameters(0 To (parameterColumns.Length - 1))

                                For loopCounter As Integer = 0 To (parameterColumns.Length - 1)
                                    parameters(loopCounter) = New ParameterValue()
                                    parameters(loopCounter).Name = parameterColumns(loopCounter)
                                Next loopCounter
                            Else
                                Return
                            End If

                            isFirstLine = False
                        Else
                            For loopCounter As Integer = 0 To (parameters.Length - 1)
                                parameters(loopCounter).Value = Nothing
                                If loopCounter <= (parameterColumns.Length - 1) Then parameters(loopCounter).Value = parameterColumns(loopCounter)
                            Next loopCounter

                            Dim credentials As DataSourceCredentials() = Nothing
                            Dim showHideToggle As String = Nothing
                            Dim encoding As String = ""
                            Dim mimeType As String = ""
                            Dim warnings As Warning() = Nothing
                            Dim reportHistoryParameters As ParameterValue() = Nothing
                            Dim streamIDs As String() = Nothing

                            Dim execInfo As New ExecutionInfo
                            Dim execHeader As New ExecutionHeader()
                            Dim SessionId As String
                            Dim extension As String = ""

                            rs.ExecutionHeaderValue = execHeader
                            execInfo = rs.LoadReport(reportPath, historyID)
                            rs.SetExecutionParameters(parameters, "en-us")
                            SessionId = rs.ExecutionHeaderValue.ExecutionID

                            Try
                                result = rs.Render(format, devInfo, extension, _
                                   encoding, mimeType, warnings, streamIDs)

                                execInfo = rs.GetExecutionInfo()

                                Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime)
                            Catch e As Web.Services.Protocols.SoapException
                                Console.WriteLine(e.Detail.OuterXml)
                            End Try

                            Try
                                reportCount = reportCount + 1
                                Dim filename As String = reportCount.ToString()

                                If Not String.IsNullOrEmpty(filenameFormat) Then filename = String.Format(filenameFormat, parameterColumns)
                                filename = String.Format("{0}.{1}", filename, format.ToLower())

                                filename = System.IO.Path.Combine(outputPath, filename)

                                Using stream As IO.FileStream = IO.File.Create(filename, result.Length)
                                    Console.WriteLine("File created.")
                                    stream.Write(result, 0, result.Length)
                                    Console.WriteLine("Result written to the file: {0}", filename)
                                End Using
                            Catch e As Exception
                                Console.WriteLine(e.Message)
                            End Try

                        End If

                    End If

                End While
            End Using
        End Using

    End Sub

    Private Function RemoveLeadingAndTrailingQuotes(textQualifiedString As String) As String

        Dim cleanString As String = textQualifiedString

        If textQualifiedString = System.Convert.ToChar(34) Then
            cleanString = String.Empty
        Else
            If Not String.IsNullOrEmpty(textQualifiedString) AndAlso textQualifiedString.Trim() <> String.Empty Then
                If textQualifiedString.Length >= 2 Then
                    If textQualifiedString.StartsWith(System.Convert.ToChar(34)) And textQualifiedString.EndsWith(System.Convert.ToChar(34)) Then
                        cleanString = textQualifiedString.Substring(1, (textQualifiedString.Length - 2))
                    End If
                End If
            End If
        End If

        Return cleanString

    End Function