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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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