SSIS 2012: A Deep Dive
into the SSIS Catalog
Phil Brammer
Phil Brammer
• Over 12 years’ experience in database
technologies, data warehousing, ETL, on-call…
• Started on Red Brick Data Warehouse. Ralph
Kimball’s product.
• Worked with Teradata at PayPal
• Operationally manages multi-terabyte instances
• Dabbles a bit in SSIS – ssistalk.com
• Microsoft MVP, SQL Server – 6th year
SSIS Pre-SQL 2012
• Born in 2005 as a replacement for DTS
• Groans or applause?
• Client focused
SSIS in SQL Server 2012
Server Focused!
Minimal, but juicy changes in the client
Cluster aware. Sort of.
DTS is no longer supported.
Visual Studio 2010 integration
SSIS Project Overview
Project-based deployments
Legacy, but not DTS
SSIS Projects
Project versioning
Limited by server configuration option
Can restore to a previous version
Can only export current version with the GUI
The number of available project versions depends
on the server configuration setting
SSIS Catalog Execution
• ISServerExec.exe
– Program Files\Microsoft SQL Server\110\DTS\Binn
– External host for SSIS package operations such as
deployment, validation, and execution
– Opens its own Named Pipe
– Microsoft.SqlServer.IntegrationServices.Server.dll
– SQLCLR stored procedures/functions
• Call ISServerExec
• Use IPC to exchange information to ISServerExec
SSIS Catalog Execution
• SQLCLR Stored Procedures/Functions
communicate via IPC over named pipes to/from
• ISServerExec uses a SqlConnection to write
logging events back to the SSIS catalog
• Examples:
– Start Execution
• catalog.start_execution
– Create Execution Dump
• catalog.create_execution_dump
– Return performance counter data
• catalog.dm_execution_performance_counters
ISServerExec Execution Flow
Unexpected Crash
/ Crash (6)
SSIS Catalog Objects
1 Assembly
1 SQL Server Agent Job
2 Logins
16 User-Defined Functions
17 User-Defined Types
30 Tables
94 Stored Procedures
• “internal” and “catalog” schemas
SSIS Catalog Security
• Objects are encrypted with database master key
– Back it up, save the password!
• All projects and sensitive parameter values are
stored encrypted. (AES_256; modifiable)
– Encrypted with symmetric key and certificate
• Certificates and keys are created for each
project/execution/environment object
• Sensitive parameters are not set after
deployment. You must configure the
project/package accordingly once deployed.
SSIS Catalog Security
• Row-level security (folders, projects,
environments, operations/executions)
• ssis_admin & sysadmin roles can access all
• catalog.explicit_object_permissions shows object
permissions that have been set for a user
• catalog.effective_object_permissions shows
object permissions that are in effect recursively
SSIS Catalog Logging
• Can set a server-wide logging level
• Four levels: None, Basic, Performance, Verbose
• Basic logging captures most log events- OnError,
OnWarning, OnInformation, OnPre/PostValidate,
• Performance logging captures OnError,
OnWarning, and great performance data – data
flow component execution timings
• Verbose logging captures everything, including
row counts transferred.
SSIS Catalog Logging
• Logging can be extended with 3rd party products
or by using the existing framework
• Custom log events are only captured in the
verbose logging level
• Script Tasks can log via the FireXXXXXX method
(FireInformation, FireError, etc…)
• New DiagnosticEx event under verbose logging
captures parameter information passed down to
child packages
SSIS Data Taps
• Two procedures:
– catalog.add_data_tap
– catalog.add_data_tap_by_guid
• A SQL-based data viewer
• With verbose logging, no need to open up
• Insert data tap to investigate issues
• Can only store files in
• CSV format
SSIS Performance Monitoring
• One DMV
– catalog.dm_execution_performance_counters
• DMV asks ISServerExec for an execution’s
statistics (or all executions if NULL is used)
• ISServerExec passes the information back via
named pipe
SSIS Dump Files
• One procedure:
– catalog.create_execution_dump
• Create an execution dump on the fly for use with
CSS or other support activities
• Stored in
• Creates .mdmp and .tmp debug files
• Can also set parameters to cause dumps
SSIS Dump Files
• To see prior event codes from an execution,
select from catalog.event_messages, casting
message_code to BINARY(4)
• Take converted message_code and set
DUMP_EVENT_CODE to trigger dump file creation
SSIS Catalog Maintenance
• One SQL Agent Job - SSIS Server Maintenance
• Two stored procedures
– internal.cleanup_server_project_version
– internal.cleanup_server_retention_window
• One database user ##MS_SSISServerCleanupJobUser##
• Above user is allowed to execute the above two
procedures and is the job owner
SSIS Catalog Maintenance
• The Server Maintenance Job purges all of the
catalog tables based on retention windows –
properties of the SSIS Catalog
• Relies on cascade deletes. Job simply deletes
from parent tables in batches of 10 records.
• Causes extreme blocking on busy systems and
can result in canceled package executions
• SQL 2012 SP1 should alleviate the canceled
execution problem by better handling blocking
SSIS Catalog and AlwaysOn
• SSISDB is supported in an AlwaysOn setup
• Remove SSISDB from the availability group
before patching
• Remember that SSISDB’s master key is
encrypted with the service master key, which is
tied to the machine hosting SSISDB.
• On failover you will get an encryption error
• Can correct manually, or use a SQL Agent job to
open the master key
• Questions and comments
• http://msdn.microsoft.com/enus/library/hh479588.aspx
• http://blogs.msdn.com/b/mattm/archive/2012/0
• http://msdn.microsoft.com/enus/library/hh213291.aspx
• http://sqlblog.com/blogs/jamie_thomson/archive
Thank you for attending!
Phil Brammer

similar documents