Friday, 23 January 2015

Calling SSRS with a WCF proxy

This is one of those things that you either need to do or you don’t.  If you don’t then good for you - if you do then the first thing to know is it isn’t as easy as it should be.

The SQL Server Reporting Services web service is an ASMX service, which means it’s intended to be called by an ASMX proxy of the kind that Visual Studio is wont to generate via the “Add Web Reference” context menu.  The only problem with Visual Studio generated proxies (for either WCF or ASMX services) is that they are a big bloated nightmare.  In theory the basicHttpBinding of WCF provides backward compatibility with ASMX web services, so you can start by running SvcUtil against the WSDL to generate a proxy in a single file – for example (assuming a local deployment of SSRS):

svcutil http://localhost:80/ReportServer/ReportExecution2005.asmx /language:c# /t:code /namespace:*,"SSRSClientDemo.Proxies" /out:ReportServerClient.cs /noconfig

The configuration is as follows:

<system.serviceModel> 
  <bindings> 
    <basicHttpBinding> 
      <binding> 
        <security mode="TransportCredentialOnly"> 
          <transport clientCredentialType="Ntlm" /> 
        </security> 
      </binding> 
    </basicHttpBinding> 
  </bindings> 
  <client> 
    <endpoint address="http://localhost:80/ReportServer/ReportExecution2005.asmx" 
              binding="basicHttpBinding" 
              contract="SSRSClientDemo.Proxies.ReportExecutionServiceSoap" 
              name="ReportExecutionServiceSoap" /> 
  </client> 
</system.serviceModel>

Note that the clientCredentialType is Ntlm – this implies a non-Kerberos deployment (which a local environment will normally be).  As a general rule of thumb, if the reporting service is on a different machine to the database (also known as a scale-out deployment) then you’re using Kerberos and need to set the clientCredentialType to Windows.

So far so good, but you’ll probably find that the proxy can’t authenticate to the reporting service.  This is because the default security mode for basicHttpBinding is none.  So we need to get the proxy to use default network credentials (i.e. your Windows login):

var reportServiceClient = new ReportExecutionServiceSoapClient(); 
reportServiceClient.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

For Kerberos deployments we need to enable delegation.  We can hard-code this but as we should aim to make everything configurable, it makes sense to derive it based on the clientCredentialType:

var impersonationLevel = TokenImpersonationLevel.None; 
var basicHttpBinding = reportServiceClient.Endpoint.Binding as BasicHttpBinding;

if (basicHttpBinding != null) 
{ 
    if (basicHttpBinding.Security.Transport != null && 
        basicHttpBinding.Security.Transport.ClientCredentialType == HttpClientCredentialType.Windows) 
    { 
        impersonationLevel = TokenImpersonationLevel.Delegation; 
    } 
}

reportServiceClient.ClientCredentials.Windows.AllowedImpersonationLevel = impersonationLevel; 

This queries the binding as set in configuration and if it’s Windows it enables delegation.

So once you’ve done that you can use the proxy as you would if it were ASMX.  The only issue is that WCF proxies are sort-of-mostly protocol agnostic so it doesn’t send useful information such as the HttpLanguage which means that both server and client regional settings will be ignored (and thus default to en-US).  Fortunately Paulo Morgado has a solution to this problem.

I’ve created a downloadable Visual Studio sample client and RDL file which demonstrates all of this.  This includes the regional settings fudge – if you look at line 30 in the HttpLanguageMessageInspector.cs file you can see where I set the culture based on Thread.CurrentThread.CurrentCulture.

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