SSIS Catalog Execution Parameter Values

When doing any new development or major overhaul of existing SSIS architecture, I almost always recommend to clients that they deploy those packages to the SSIS catalog. Using the catalog to store and execute SSIS packages takes a lot of the manual work out of development and maintenance, particularly when it comes to package logging.

SSIS Catalog Execution Parameter Values

When you execute a package in the SSIS catalog, one of the critical pieces of information collected is the list of parameter values used during the execution of that package. There are three different types of parameter values that are logged here:

  • Any user-defined parameters that exist in either the project or the package. These parameters will be logged even if you do not set a value for the parameter (it’ll simply be logged as a NULL value in that case).
  • The property settings for each connection manager in the package and project. Note that these values will be logged even if you have not attached a user-defined parameter to those properties. These connection string settings will all have the prefix “CM.” on the parameter name.
  • The system parameters for that execution, including LOGGING_LEVEL and SYNCHRONIZED. Like the above connection string parameters, these values will be logged even if you don’t explicitly set them.

These runtime values are logged in the SSISDB database, written to the table internal.execution_parameter_values. As with most of the tables in the internal schema, there is a corresponding view of the same name in the catalog schema that abstracts the most commonly-used columns in each table. Shown below is a sample set of values from the catalog.execution_parameter_values table for a single execution.

image

As shown, this view reports the execution ID, parameter type (20 = project parameter, 30 = package parameter, and 50 = system parameter), the parameter name, and the runtime value. Note that in the underlying internal.execution_parameter_values table, there is another column that stores the encrypted value for sensitive parameters (those representing non-NULL passwords, or those explicitly marked as Sensitive), but there should be few if any legitimate needs to query on those encrypted values.

The fact that these parameters are logged in the SSIS catalog tables will be useful for troubleshooting and auditing purposes. If you need to review a prior package execution, you’ll likely need this information to confirm the parameter values that were used during execution.

With respect to the values stored in the internal.execution_parameter_values table, you’ll want to casually keep an eye on the size of that table, particularly on busy SSIS servers. I’ve seen this table grow to tens of millions of records within a year, which is not necessarily a bad thing but it is a metric that you’ll need to plan for.

Conclusion

The internal.execution_parameter_values table (and its commonly used abstraction, the catalog.execution_parameter_values view, are useful for when you need to review the list of runtime values used during past SSIS package executions. This and the other automatically-configured logging tables are some of the biggest advantages for using the SSIS catalog for storage and execution of SSIS assets.

About the Author

Tim Mitchell
Tim Mitchell is a data architect and consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or training? If so, contact Tim for a no-obligation 30-minute chat.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.