SSIS Catalog: Part 4 – Execution

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply