As
a MSBI developer, I always enjoys while deploying of SSRS reports and SSAS cubes but not in case of SSIS packages deployment because we have project level single click deployment in SSRS and SSAS, here our life is so easy
but I always feel difficult with SSIS packages legacy deployment and management of SSIS
packages. There we need to deploy SSIS packages individually either in file
system or system database MSDB
Good
News for SSIS Developers is, in SQL server 2012 Microsoft introduced SSIS Package Deployment Model i.e.
single click project deployment. Also it introduced new specific database for
only SSIS Package Deployment called SSISDB. Now in this topic I cover what
it is SSIS Package Deployment Model and how to deploy your SSIS packages to SSISDB
In SQL Server 2012, new major
changes in SSIS are project deployment
and managing the configurations. By this major introductions, we can stop
completely using traditional configurations like Parent package, XML
configurations and environment variable package configuration. In new project
deployment, it added features called Project parameter and Package parameters, Environments,
Environment variables and Environment references
Project
Deployment Model
As we discussed above, Project Deployment Model is the new deployment model for SSIS projects. If we open new SSIS solution and create new packages under this solutions, by default all SSIS projects are project deployment model only. If we want to change back to old Legacy Deployment model, we can also migrate to it.
As we discussed above, Project Deployment Model is the new deployment model for SSIS projects. If we open new SSIS solution and create new packages under this solutions, by default all SSIS projects are project deployment model only. If we want to change back to old Legacy Deployment model, we can also migrate to it.
When we
create a project with this model and build our solution, an .ispac will create
under bin/Development folder. This .ispac will include all our packages and
parameters
Integration
Service Catalog
In previous deployment models we used to store packages either in File system or system Database MSDB. In SSIS 2012, Integration Services catalog introduced to store all deployed packages, parameters and logging of deployed packages executions (like when package started ,When package got failed ) in SQL server database and Integration Services catalog used Encrypt the data to store sensitive data .
Integration Services catalog also stores multiple versions of deployed packages. In Integration Services catalog all deployed SSIS projects, packages, parameters, environments and all package executions (operational) history will be stored in the SSISDB database .Suppose if we have 100 SSIS packages and all 100 packages runs every few minutes , In SSISDB it will store all historical information and hence our SSISDB database growth will increase day by day rapidly you could see how the database storing all the historical information would grow exponentially. So we need to configure SSISDB as per our requirement
Below are some basic configurations on SSISDB to log Operational information
Integration Services catalog also stores multiple versions of deployed packages. In Integration Services catalog all deployed SSIS projects, packages, parameters, environments and all package executions (operational) history will be stored in the SSISDB database .Suppose if we have 100 SSIS packages and all 100 packages runs every few minutes , In SSISDB it will store all historical information and hence our SSISDB database growth will increase day by day rapidly you could see how the database storing all the historical information would grow exponentially. So we need to configure SSISDB as per our requirement
Below are some basic configurations on SSISDB to log Operational information
- Retention Period in Days – Defaults to 365
- Default Logging Level – Defaults to Basic
- Maximum Number of Old Versions Per Project – Defaults to 10
- Whether to Periodically Remove Old Versions – Defaults to True
Project
Parameters and Package Parameters
In SSIS 2012, we create package parameters and Project parameters apart from variables in packages. This parameters can be access inside packages and can set values of package components like connection strings, variable values.
In SSIS 2012, we create package parameters and Project parameters apart from variables in packages. This parameters can be access inside packages and can set values of package components like connection strings, variable values.
The only
difference between project parameters and package parameters is the scope
access. A package parameter can be access inside particular package only but
the project parameter can access global to the project. Parameters can also
store sensitive data and stored in encrypted form in the catalog.
Difference
between parameter and Variable is once package execution starts parameter never
change but variable value can be change while executing package
By
three types we can assign value to parameter
1.
Static value given to parameter in SSIS project or package
2. Catalog
level value will pass to SSIS project/package parameter
3. Environment
variable will pass to Catalog level and then overwrites SSIS project/package parameter
Environments
and Environment variables
Again concept of environments and environment variables are introduced in SSIS 2012. As part of automation SSIS comes with this new feature. With this environments and environment variables, even layman can easily deploy SSIS packages in any environment without any difficult.
Again concept of environments and environment variables are introduced in SSIS 2012. As part of automation SSIS comes with this new feature. With this environments and environment variables, even layman can easily deploy SSIS packages in any environment without any difficult.
On top level,
an environment is placeholder for environment variables. These environment
variables values are Static values . In Integration Services catalog we can
create any number of environment and we can create environment variables inside
environment. Environment is like ‘DEV’,’TEST’ and ‘PROD’.
For
deployed projects we can tag only one environment at a time and assign environment
variables to project parameters /Package parameters. Due to that environment
variable values will overwrite project parameters /Package parameters
sp_configure 'show advanced options', 1;
Demonstrate Project Deployment Model
To demonstrate this SSIS Deployment model, now I will create one
simple SSIS project and I will show them how to deploy in Integration Services Catalog.
And also, I will create DEV and QA environments and environment variables inside
DEV and QA environments. Finally I will map environment to project and explain
how environment variables have to configure. Based on environment we choose variable
values inside that environment will pass to the project. For example if I choose
DEV environment, then DEV environment variables will overwrite values of
project /Package Parameters inside
Following steps will cover as part of Integration Services Catalog
1. Configure
Integration Services Catalog
1. Configure Integration Services Catalog
To make use of new features of
Integration Services, we need to create an Integration Services Catalog in SQL
Server instance. Only single Catalog we can able to create in single SQL Server
instance.
Integration Services Catalog
uses common language runtime (CLR) Stored procedures. But common language
runtime (CLR) integration feature is off by default . To enable we need to use sp_configure
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
In create catalog ,in SQL Server Management studio,right click on
node of Integration services and click on Create Catalog option.
Next, it will popup one window and shows database name as SSISDB.
There we need to give Password. Why we need to give password because in SSISDB,
it will store sensitive data. So to encrypt sensitive data we need password
.
After integration
Services Catalog is created, we can see SSISDB in SQL Server. In Integration
Services Catalog, it saves all deployed packages and its operational logging
data in SQL Server SSISDB and in this database we can able to see predefined
tables ,views and system procedures to store all deployed packages information.
In Integration Services Catalog on we can deploy many projects
deployed and also we create several folders to easy navigation and management. In
Below example I have created FirstProject folder for my project
We can create a folder by just right click in SSISDB node and
choose Create Folder. Next it will open the Create Folder window as shown
below; give a name for the folder to create :
After we create any folder in Integration Services Catalog, we can able to see two sub folders under newly created folder.
After we create any folder in Integration Services Catalog, we can able to see two sub folders under newly created folder.
Projects: A place
holder for deployed projects inside Folder
Environments: A placed holder for environments like DEV and
QA
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
