In previous post of SSIS Catalog series, I’ve explained how to create SSIS catalog and configure properties. In this post I talk about Folder hierarchy in SSIS catalog, and different parts of folder hierarchy: Folder, Projects, packages and Environment Variables.
SSIS Catalog contains folders, in each folder there can be one or more projects. each project contains SSIS packages. Environments provides multiple configurations for deployed package, and can be connected to projects or packages.
SSIS Catalog Hierarchy
Folders
These stored procedures from SSISDB catalog database deals with folders:
catalog.create_folder : creates a folder, folder_name is input parameter and folder_id is output parameter. Note that all folders will be created in the root of SSISDB, so there is no need to set the path.
catalog.rename_folder : renames a folder, inputs: old_name and new_name
catalog.delete_folder : deletes a folder, input: folder_name.
catalog.set_folder_description : sets a description for the folder, folder_name and folder_description are input parameters.
This view shows all folder information: catalog.folders
Projects
Projects can be deployed into SSIS package from SSDT or by other deployment methods.
There are some stored procedures for dealing with projects;
catalog.deploy_project : deploy a binary SSIS project file to SSIS catalog. input parameters: folder_name, project_name, projectstream (SSIS project binary file). output parameter: operation_id
catalog.get_project : returns binary stream of the project. input parameters: folder_name and project_name
catalog.move_project : moves a project from source folder into destination folder. input parameters: source_folder, project_name and destination_folder.
catalog.delete_project : delete the project. input parameters: folder_name and project_name.
catalog.restore_project : restore the project to specific version number. input parameters: folder_name, project_name and object_version_lsn.
these views provides information about projects:
catalog.projects, catalog.packages and catalog.object_versions.
Environment Variables
when you deploy a SSIS project into a server, you want want to run that project with different configurations. and you want to keep those configuration stored somewhere. Here Environments comes to play, they provide ability to create multiple configurations.
you can create environments under each folder, each environment can consists of different variables and their values.
you can connect the environment to whole SSIS project or each individual SSIS package. after connect environment you can assign each environment variable to a package parameter, connection string or other dynamic properties of package or project. connection between environments and projects can called as environment reference.
picture below shows how environment variables connects to projects
picture below shows how environment variables can be assign to a package at the time of execution
There are some stored procedures to work with Environments:
catalog.create_environment, catalog.rename_environment, catalog.move_environment, catalog.delete_environment and catalog.set_environment_property
There are some stored procedures to work with environment variables:
catalog.create_environment_variable, catalog.set_environment_variable_property, catalog.set_environment_variable_value and catalog.delete_environment_vairable
for connecting environments with projects these stored procedures are available:
catalog.create_environment_reference, catalog.set_environment_reference_type and catalog.delete_environment_reference
environment reference type can be A for absolute or R for relative. absolute means that environment will be addressed from exact folder name. and relative means that environment addressed from existing folder.
picture below shows how to create environment references in SSMS GUI:
database schema of tables which store information about folder, projects, packages and environment showed in below image
all tables above are in INTERNAL schema of SSISDB database.
in above schema there are some tables for permission; folder_permissions, project_permissions and environment_permissions.
permissions can be also set via SSMS GUI like screenshot below:
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.
Good Stuff Reza! Very helpful!