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
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 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.