SSIS Catalog: Part 3 – Folder Hierarchy; Folder, Projects and Packages

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

1 thought on “SSIS Catalog: Part 3 – Folder Hierarchy; Folder, Projects and Packages

Leave a Reply