What Is The SSIS Catalog and Why Do

What Is The SSIS Catalog and Why Do I Care? Stan Geiger Sr. Product Manager – BI Tools Idera @MSBI_Stan | stan. geiger@idera. com

About Me § Sr. Product Manager with Idera § Performance Monitoring of Microsoft BI stack § Backup and Recovery of Microsoft SQL Server § § Geek Sync Presenter Blog Contributor HSSUG presenter Over 25 years experience § § BI, Data Architect DBA Developer Data Analyst

Agenda § § § § Introduction SSIS Catalog Overview SSIS Project Deployment Execution and Logging SSISDB Practical Examples

SSIS Catalog § Catalog Creation § Catalog Objects § § Projects Packages Parameters Environments § Companion database SSISDB

SSIS Catalog SQL Server Database Engine Integration Services Catalog Folder Projects Parameters Environment References Environments Packages Environment Variables

SSIS Catalog

SSIS Project § Parameters § Assigns values at time of execution § Package parameters § package scope § Expressions allowed § Project Parameters § Shared among all packages in the project § Literal values

Deployment § Project Based § Visual Studio § SSMS § isdeploymentwizard. exe § § Versioning Parameters Environment variables § Assign to parameters during package execution § Configurable

Deployment § Environments § Use with project parameters § Multiple configurations (test, prod, dev, etc. )

Deployment § Visual Studio Example • • • Project level connections Parameterized connections

Execution and Logging § Execution Methods § § SSMS dtexec SQL Server Agent Stored Procedure (SSISDB database)

Execution and Logging § Logging Levels § None § Basic (default) § All events except custom and diagnostic events § Performance § On. Error and On. Warning events § Performance Statistics § Verbose § Kitchen Sink § Customized (SSIS 2016)

Execution and Logging § Examples

Execution and Logging § Stored Procedure Example § Execution Reporting § Logging Dependent § Default Reports § Custom Reports

SSISDB § Catalog Database § Tables § Execution information § Configuration information § Views § Stored Procedures § Package Execution § Administration § Maintenance

SSISDB • • • executions - executables inside the package, parameter values and data taps. executables – all executable objects within a package. executable_statistics – data about all executable objects execution_component_phases – log referring to each data flow execution_data_statistics – total rows sent through the data flows execution_parameter_values – parameter values at time of execution

SSISDB • • • operations - information about each operation run in the SSIS catalog. validations– validation of projects which contains folder name and package name. event_messages – all messages passed during events operation_os_sys_info – cpu count, available physical memory and total physical memory at the time of operation event_message_context - conditions that are associated with execution event messages

Questions? Demo Stan Geiger Sr. Product Manager – BI Tools Idera @MSBI_Stan | stan. geiger@idera. com