Transcript Configuration Set values for parameters Central connection manager configuration Advanced property override functionality Security Management Encryption of projects and parameter values Interactive package execution and SQL Agent integration Row-level security to control access.

Configuration
Set values for parameters
Central connection manager
configuration
Advanced property override
functionality
Security
Management
Encryption of projects and
parameter values
Interactive package execution
and SQL Agent integration
Row-level security to control
access to packages
Dashboard and built in reports
for troubleshooting
PowerShell
SSMS
OM: Manage
OM: Manage
Deployment Wizard
OM: Manage
OM: Project
Application
OM: Manage
OM: Project
SSIS Server
SQL Server Instance
Execution Process
Deploy
Manage
Security
Validate
IS Objects
Operation logs
Security
State
Execute
Execution Control
Runtime
Components
SSIS Catalog
• Provides a set of stored procedures and views for managing,
configuring, executing and monitoring SSIS packages
deployed to the SSIS Catalog
• The Catalog schema is designed for customer use, and is a
supported API
• Contains views, stored procedures, and functions
• The Internal schema is neither public nor supported
• Contains base tables and lower-level objects
SSISDB
• All projects and sensitive values are
encrypted
• Protected by a pair of certificate and symmetric key
• Utilizes the built-in SQL Server encryption infrastructure
• http://blogs.msdn.com/b/mattm/archive/2012/03/23/ssiscatalog-backup-and-restore.aspx
Whatever you do
using SSMS can be scripted
either using T-SQL or PowerShell
Important: Backup the
database master key!
SSISDB
Stored
Procedures
Tables
Functions
Stored
Procedures
Views
SQLCLR Assembly
Triggers
Internal Catalog
ISServerExec.exe
Microsoft.SqlServer.IntegrationServices.Server.
Shared.dll
Public objects:
•
•
•
•
•
42 store procedures
25 views
3 functions
1 trigger
1 SQL CLR assembly
SQL Server
instance
Client
T-SQL sp
Client
SQL Server
instance
Entry point:
T-SQL sp
SQL Server External Process
instance
ISServerExec
Client
Entry point:
T-SQL sp
Invoked
CLR SP
Invoked
CLR
sp
Return
success/fail
Throw if
errors
occur
CASE A
T-SQL sp
Return
success/fail
Throw if
errors
occur
CASE B
T-SQL sp (entry point)
T-SQL sp invokes managed sp
Create process by
impersonating
caller of the stored
proc
Return success/fail
Throw if errors
occur
CASE C
Either Asynchronous or
Synchronous
T-SQL sp (entry point)
T-SQL sp invokes managed sp
Managed sp creates external process by
impersonating caller of sp
Project
Parameter
values
Environment
values
Project Symmetric key
Environment
Symmetric key
Execution Symmetric
key
Project Certificate
Environment
Certificate
Execution Certificate
Database Master key
Execution
Parameter
values
SET @key_name =
'MS_Enckey_Proj_'+CONVERT(varchar,@project_id)
SET @certificate_name =
'MS_Cert_Proj_'+CONVERT(varchar,@project_id)
OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE
certificate_name
SELECT parameter_name,
DECRYPTBYKEY([sensitive_parameter_value])
FROM internal.[object_parameter_values]
WHERE [project_id] = @project_id
CLOSE SYMMETRIC KEY key_name
Service Master Key
Supported 'TRIPLE_DES_3KEY', 'AES_128', 'AES_192', 'AES_256'
T-SQL functions: EncryptByKey; DecryptByKey
Note: We do not support sensitive data with length > 8000
Sales
HR
HR DW project
SAP migration project
Groups of packages (anywhere)
Projects
<xml>
x\y\MyTask\Server = “TestServer”
ServerName is String
HR DW project
x y
Configurations
Parameters
BatchNumber is Int32
internal.
catalog_encryption_keys
key
CLR Cryptography
Project binary
Project Symmetric key
TripleDESCryptoServiceProvider
Project Certificate
AesCryptoServiceProvider(192)
AesCryptoServiceProvider(128)
Database Master key
Service Master Key
AesCryptoServiceProvider(256)
• ISServerExec.exe – External host for SSIS package operations (deploy, validate and
execute)
• ISServer Assembly
• UNSAFE Assembly granted to
##MS_SQLEnableSystemAssemblyLoadingUser##
• Created from Microsoft.SqlServer.IntegrationServices.Server.dll
SSISDB
ISServerExec
Processes
• SQLCLR stored procedures used for
• Deploy, validate, and execute require impersonation when starting external
process
• IPC communication with ISServerExec
SqlConnection
SSIS Events
ISServerExec
Named Pipe Server
SSISDB
Logging Events
Writes back to SSISDB events that are produced during package execution
IPC between ISServerExec and Stored Procedures
• CLR stored procedure sends command to ISServerExec
Examples
• Get me all the per-instance performance counters
• Stop Operation
• Create execution dump
• ISServerExec
• Performs the operation
• Sends back information via named pipes
Named Pipes
EXEC [SSISDB].[catalog].[create_execution]…
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, …
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, …
EXEC [SSISDB].[catalog].[start_execution] @execution_id
SSIS Events
ISServerExec
Named Pipe Server
SSISDB
catalog.executables
catalog.executable_statistics
Updated when the OnPostExecute event
for each component if fired
ADO.Net
IDTSEvents
Components
Events
Listener
SSISDB
Tables
API
IDTSLogging
CreateProcessAsUser
Log
Provider
Views
Named Pipe
TVFs
SqlServr.exe
Reports
ISServerExec
ISServerExec.exe
OM/Engine
Built-In Reports
Component Timing & Row Counts
Validate
Pre Execute
ProcessInput
ProcessInput
Post Execute
SELECT package_name, task_name, subcomponent_name,
SUM(DATEDIFF(ms,start_time,end_time)) as active_time,
DATEDIFF(ms,min(start_time),max(end_time)) as total_time
FROM catalog.execution_component_phases
WHERE execution_id = 1841
GROUP BY package_name, task_name,
subcomponent_name, execution_path
ORDER BY package_name, task_name,
subcomponent_name, execution_path
http://ssisreportingpack.codeplex.com
http://www.mattmasson.com/2013/04/monitoring-ssis-package-executions/
WITH DiagnosticExTable
(EventMessageID, EventName, MessageSourceName, XmlData)
AS (
SELECT event_message_id,event_name,message_source_name,cast( message as xml)
FROM catalog.event_messages m
WHERE m.operation_id = 16
AND m.event_name = 'DiagnosticEx'
)
SELECT EventMessageID,Eventname,MessageSourceName,
parameter.value('declare namespace DTS=''www.microsoft.com/SqlServer/Dts'';
(@DTS:ObjectName)[1]','nvarchar(260)') as ParameterName,parameter.value(
'declare namespace DTS=''www.microsoft.com/SqlServer/Dts'';
(DTS:Property/text())[1]',
'nvarchar(256)') as ParameterValue
FROM DiagnosticExTable
CROSS APPLY XmlData.nodes('declare namespace DTS=''ww.microsoft.com/SqlServer/Dts'';
(/DTS:ParameterValues/DTS:PackageParameter)') as PackageParameter(parameter)
Results
SELECT *
FROM catalog.dm_execution_performance_counters
(<your execution ID value>)
SSIS Events
What are the values for the
Perf Counters?
ISServerExec
Named Pipe Server
Perf Details
SSISDB
EXEC catalog.create_execution_dump @execution_id = 88
Pause running package
Dump
Resume running package
SSIS Events
Create execution Dump
ISServerExec
Named Pipe Server
SSISDB
DumpFile
-- Create the data tap on a data flow path in the
package
exec catalog.create_execution …
exec catalog.add_data_tap
@execution_id,
'\Package\DFT Load Dim Vendor',
'Paths[SRC DimDCVendor.OLE DB Source Output]',
'DCVendorOutput.csv'
exec catalog.start_execution @execution_id …
Data Tap Files
Windows
Azure
mva
http://channel9.msdn.com/Events/TechEd
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
(5) Stop execution
SSISDB
(1) Named pipe client
Create dump
(3) pipe
Perf data
ISServerExec.exe
Query perf data
(2) Named pipe server
You can use
pipelist.exe to
check the named
pipe used
(4) Listen to the command
(6) Perform action
catalog.stop_operation
catalog.create_execution
catalog.start_execution
Created (1)
Running
(2)
Pending
(5)
Success
(7)
Completed
(9)
Stopping
(8)
ISServerExec
Unexpected Crash
Failed
(4)
Canceled
(3)
Unexpected
Termination
/ Crash (6)
SSISDB
http://msdn.microsoft.com/en-us/library/ff878037
http://msdn.microsoft.com/en-us/library/ff878149.aspx
Permissions
Read
Modify
⦁
⦁
⦁
⦁
⦁
⦁
⦁
⦁
Execute
Securable
Folder
Project
Environment
Operation /
Execution
⦁
Manage
Permission
⦁
⦁
⦁
⦁
Create New Read Objects
⦁
⦁
Modify
Objects
Execute
Objects
Manage
Objects
Permission
⦁
⦁
⦁
Troubleshooting Permissions
• Check explicit (raw) permission entries from view [catalog].[explicit_object_permission]
• Effective (valid) permission is a computation result from explicit permission items:
[catalog].[effective_object_permissions]
• The server does not check permissions if a user is sysadmin/ ssis_admin
http://technet.microsoft.com/enus/library/cc966395.aspx