Server – Matt Masson

Links from the SSIS Roadmap Session

Here are some of the resources I mentioned in the SSIS Roadmap session at the PASS Summit.

SSIS Reporting Pack from Jamie Thomson

DQS Matching Transform from OH22 Data

DQS Domain Value Import Destination from OH22 Data

There is also a great series of blog posts (part 1, part 2, part 3) on using these transforms on the DQS team blog.

Publish to SSIS Catalog using PowerShell

Here is a PowerShell script that I use to provision and setup SSIS Catalogs when I’m demoing SQL Server 2012. It does the following:

Connects to localhost (default instance)
Drops the existing SSIS Catalog
Creates a new SSIS Catalog
Creates a Folder
Deploys a Project
Creates an Environment
Creates three server varaibles
Creates an environment reference for the project

Be careful of step #2 above – this script is great for resetting a demo environment, but you’ll want to modify it if you’re using it for real deployments!

# Variables
$ProjectFilePath = "C:SSISMyCatalogProjectbinDevelopmentMyCatalogProject.ispac"
$ProjectName = "MyCatalogProject"
$FolderName = "Demo"
$EnvironmentName = "CustomerA"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

Write-Host "Removing previous catalog ..."

# Drop the existing catalog if it exists
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() }

Write-Host "Creating new SSISDB Catalog ..."

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1")
$catalog.Create()

Write-Host "Creating Folder " $FolderName " ..."

# Create a new folder
$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
$folder.Create()

Write-Host "Deploying " $ProjectName " project ..."

# Read the project file, and deploy it to the folder
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

Write-Host "Creating environment ..."

$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
$environment.Create()

Write-Host "Adding server variables ..."

# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
$environment.Variables.Add(“CustomerID”, [System.TypeCode]::String, "C111", "false", "Customer ID")
$environment.Variables.Add(“FtpUser”, [System.TypeCode]::String, $EnvironmentName, "false", "FTP user")
$environment.Variables.Add(“FtpPassword”, [System.TypeCode]::String, "SECRET1234!", "true", "FTP password")
$environment.Alter()

Write-Host "Adding environment reference to project ..."

# making project refer to this environment
$project = $folder.Projects[$ProjectName]
$project.References.Add($EnvironmentName, $folder.Name)
$project.Alter()

Write-Host "All done."

# Variables

$ProjectFilePath = "C:SSISMyCatalogProjectbinDevelopmentMyCatalogProject.ispac"

$ProjectName = "MyCatalogProject"

$FolderName = "Demo"

$EnvironmentName = "CustomerA"

# Load the IntegrationServices Assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time

$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server

$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object

$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

Write-Host "Removing previous catalog ..."

# Drop the existing catalog if it exists

if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() }

Write-Host "Creating new SSISDB Catalog ..."

# Provision a new SSIS Catalog

$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1")

$catalog.Create()

Write-Host "Creating Folder " $FolderName " ..."

# Create a new folder

$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")

$folder.Create()

Write-Host "Deploying " $ProjectName " project ..."

# Read the project file, and deploy it to the folder

[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)

$folder.DeployProject($ProjectName, $projectFile)

Write-Host "Creating environment ..."

$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")

$environment.Create()

Write-Host "Adding server variables ..."

# Adding variable to our environment

# Constructor args: variable name, type, default value, sensitivity, description

$environment.Variables.Add(“CustomerID”, [System.TypeCode]::String, "C111", "false", "Customer ID")

$environment.Variables.Add(“FtpUser”, [System.TypeCode]::String, $EnvironmentName, "false", "FTP user")

$environment.Variables.Add(“FtpPassword”, [System.TypeCode]::String, "SECRET1234!", "true", "FTP password")

$environment.Alter()

Write-Host "Adding environment reference to project ..."

# making project refer to this environment

$project = $folder.Projects[$ProjectName]

$project.References.Add($EnvironmentName, $folder.Name)

$project.Alter()

Write-Host "All done."

Exit Codes, DTEXEC, and SSIS Catalog

DTEXEC has two execution modes when running packages in the SSIS Catalog.

By default, the process runs asynchronously – the package execution is scheduled on the server, and DTEXEC returns immediately. DTEXEC does not wait for the package execution to complete. In this case, %ERRORLEVEL% would represent whether the package execution was created successfully, and will not reflect the result of the execution.

To get the result of the package execution, you can set the $ServerOption::SYNCHRONIZED property to True. In this mode, DTEXEC will wait for the package execution to complete. If the execution was successful, the %ERRORLEVEL% will be set to 0. If it failed, it will be set to 1.

See the examples below – the first execution runs a package (2-RowCounts.dtsx) on my local instance, and completes successfully. The second execution runs a package (6-ErrorContext.dtsx) that will fail.

C:>dtexec /ISserver SSISDBDemoMyCatalogProject2-RowCounts.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.XX.XX for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  9:49:16 AM
Execution ID: 11.
To view the details for the execution, right-click on the Integration Services Catalog,
and open the [All Executions] report
Started:  9:49:16 AM
Finished: 9:49:22 AM
Elapsed:  6.015 seconds

C:>echo %ERRORLEVEL%
0

C:>dtexec /ISserver SSISDBDemoMyCatalogProject6-ErrorContext.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.XXX.XX for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  9:49:43 AM
Package execution on IS Server failed. Execution ID: 12, Execution Status:4.
To view the details for the execution, right-click on the Integration Services Catalog,
and open the [All Executions] report
Started:  9:49:43 AM
Finished: 9:49:49 AM
Elapsed:  6.235 seconds

C:>echo %ERRORLEVEL%
1

C:>dtexec /ISserver SSISDBDemoMyCatalogProject2-RowCounts.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True

Microsoft (R) SQL Server Execute Package Utility

Version 11.0.XX.XX for 64-bit

Started: 9:49:16 AM

Execution ID: 11.

To view the details for the execution, right-click on the Integration Services Catalog,

and open the [All Executions] report

Started: 9:49:16 AM

Finished: 9:49:22 AM

Elapsed: 6.015 seconds

C:>echo %ERRORLEVEL%

C:>dtexec /ISserver SSISDBDemoMyCatalogProject6-ErrorContext.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True

Microsoft (R) SQL Server Execute Package Utility

Version 11.0.XXX.XX for 64-bit

Started: 9:49:43 AM

Package execution on IS Server failed. Execution ID: 12, Execution Status:4.

To view the details for the execution, right-click on the Integration Services Catalog,

and open the [All Executions] report

Started: 9:49:43 AM

Finished: 9:49:49 AM

Elapsed: 6.235 seconds

C:>echo %ERRORLEVEL%

For more details on the new DTEXEC command line parameters for the SSIS Catalog, see:

Using DTEXEC with Packages on the IS Server (SSIS Team Blog)
dtexec Utility (Books Online)

Queries for Data Flow Component Performance in SQL 2012

The built-in SSIS Catalog reports in SSMS show a breakdown of task and component performance of a package execution when you run with a logging level of “Performance”.

For a more detailed or customized view, you can query the catalog tables directly. The key table to get data flow performance information is [catalog].[execution_component_phases]. Here are two sample queries – the first returns the total time spent on each component (similar to what is displayed in the performance report), and the second returns the times broken down by phase.

--
-- Determine the execution id for the last package execution
--

DECLARE @execution_id bigint;
SET @execution_id = (SELECT MAX(execution_id) from [catalog].execution_component_phases)

--
-- Break down time spent per transform
--

SELECT	package_name as PackageName,
        subcomponent_name as Transform,
        SUM(DATEDIFF(ms,start_time,end_time)) as ActiveTime
FROM	[catalog].execution_component_phases
WHERE   execution_id = @execution_id
GROUP BY package_name, task_name, subcomponent_name, execution_path
ORDER BY ActiveTime DESC

--
-- Break down transforms per phase
--

SELECT  package_name as PackageName,
        subcomponent_name as Transform,
        phase as Phase,
        SUM(DATEDIFF(ms,start_time,end_time)) as ActiveTime
FROM	[catalog].execution_component_phases
WHERE   execution_id = @execution_id
GROUP BY package_name, task_name, subcomponent_name, execution_path, phase
ORDER BY ActiveTime DESC

-- Determine the execution id for the last package execution

DECLARE @execution_id bigint;

SET @execution_id = (SELECT MAX(execution_id) from [catalog].execution_component_phases)

-- Break down time spent per transform

SELECT package_name as PackageName,

subcomponent_name as Transform,

SUM(DATEDIFF(ms,start_time,end_time)) as ActiveTime

FROM [catalog].execution_component_phases

WHERE execution_id = @execution_id

GROUP BY package_name, task_name, subcomponent_name, execution_path

ORDER BY ActiveTime DESC

-- Break down transforms per phase

SELECT package_name as PackageName,

subcomponent_name as Transform,

phase as Phase,

SUM(DATEDIFF(ms,start_time,end_time)) as ActiveTime

FROM [catalog].execution_component_phases

WHERE execution_id = @execution_id

GROUP BY package_name, task_name, subcomponent_name, execution_path, phase

ORDER BY ActiveTime DESC

Running these queries returns something like this:

Some notes:

You need to run the package with a Performance logging level to capture this data
You can set a default logging level for the Catalog
The query above determines the execution_id for the latest run – you will need to change this value to look at the results for specific package executions

Setting a Default Logging Level in the SSIS Catalog

One of the powerful features of the SSIS Catalog is that it can automatically capture logs for packages run on the server. The logging level is set to Basic by default, and can be changed when the package is run on the Advanced tab of the run UI (as shown below. This can also be done when scheduling the package through SQL Agent).

You can set a server wide default logging level on the Catalog properties page.

Connect to the SQL Server instance using SSMS
Expand the Integration Services Catalogs node
Right click on the SSISDB node, select Properties
Change the Server-wide Default Logging Level setting (in the Operations Log category)

See What Events are Included in the SSIS Catalog Log Levels for more information.