SSIS Catalog: Part 4 – Execution

In previous blog post of SSIS Catalog series, I described folder hierarchy. In this post I will explain tables which deals with execution of packages and projects.

At the time of package’s execution, you can map environment variables to package’s parameters, set values to the connection string’s properties, set package’s property values, and set 32bit run time, and logging level.

Execute Package Window

When you right click on a package in SSMS, you can select Execute from popup menu and then Execute Package window will appear.

in the first tab,  you can select environment variable and set each variable to a package parameter. or you can set parameter values directly.

in the second tab, you can see package’s connection strings. and set value of each property related to that connection string like InitialCatalog, password …

in the third tab, you can set other properties of package and their values. also in this tab you can set Run 32 bit runtime (this is useful when you want to run the SSIS package under 32 bit specially when you want to work with Jet Engine which works more proper in 32bit than 64 bit). you can also set Logging Level of this execution of package simply with selecting appropriate logging level from the drop down list.

SSIS Catalog Tables for Execution

SSISDB catalog database contains some tables in the INTERNAL schema which stores information about execution of package, executables inside the package, parameter values and data taps. below you will see a short description of each table and few important columns with sample data rows of that table.

Execution

Execution_id,
Package name, Project name, Folder name, environment, Use 32bit runtime

With every execution of package, an Execution_id will be
assigned.

executables

Information about each executable object in packages

Contains: executable_id, name, package path (path of
executable in the package) …

Executable_statistics

Detailed execution log for each executable.

Contains: executable_id, execution_id, duration, start_time,
end_time …

Execution_component_phases

Detailed execution log for each data flow component.

Contains: subcomponent_name (data flow components),
execution_id, phase…

Execution_data_statistics

Number of rows sent through data flow and few other details

Contains: Rows_sent, execution_id, source_component,
task_id…

Execution_data_taps

Information about data taps

Contains: dataflow_path_id_string (where data tap attached),
filename (data tap file), execution_id …

Execution_parameter_values

Information about parameters and their values at the time of
execution.

Contains: object_type (10: folder, 20: project, 30: package,
40: environment, instance of execution: 50), parameter_data_type,
parameter_name, parameter_value…

Execution_property_overried_values

Information about property values that set at the time of
execution of package and overrides default values.

Contains: property_id, execution_id, property_path,
property_value …

Whole schema of execution tables


SSIS Catalog Stored Procedures for Execution

There are some catalog stored procedures under CATALOG schema which helps in execution of a package. you can see a short description and sample execution script of them here:

http://rad.pasfu.com/index.php?/archives/70-SSIS-2012-Execute-Package-from-Another-Project.html

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply