Friday, January 29, 2016

Project deployment model SQL Server 2012(SSIS)

This is new type of deployment model in SQL Server 2012(SSDT) called project deployment model. This can create one packet for all the packages in a single ISPAC extension file with this you can deploy package without hassle. This model includes package parameter, environment variables and environment references.
  1. By default ssis packages created in project deployment model, you can revert to original version
  2. Deployment packet is created with ispac extension.
  3. This packet can not contain any additional data. Except packages information
Steps to follow create the SSIS Project deployment model
Create SSIS Catalog in SQL Server to store. It stores application related data and to encrypt sensitive data
After creation on SSISDB. Go to integration service catalog and create one folder for each different project deployment. So that you can identify the package/project based on the folder.

After creating the folder you will find two things under SSISDB Main catalog
Reason for storing environment variable is deploy the packet in any environment like test, prod, uat
Now Create the SSIS Project deployment Model
Create one sample package in SSIS IDE
By default SSIS will create in Project deployment model. If you want you can convert it into package deployment model

Now depending on the Environment we need to move the data from one db to another for ex:- if you move package into TEST environment. Data need to move from test environment database to test environment data, likewise UAT db to uat db.
Now create one project parameter which will hold name of database which is passed to an environment and used in the target connection to connect target database. To create project parameter go to click on
Right click on project parameter open it. And click on new project parameter

  • Name:-  DatabaseName
  • Scope:- Project
  • Data type:- String
Defaultvalue:- Database info which you want hold in project params here am using testdb

And the project also contain connection manager (source and destination). To make connection manager dynamic you need to configure expression for the connection.
Right click on the connection manager and go to properties and edit the expression as show below
If you have many number of connection managers create many number of project parameters and map it to corresponding database name. Now execute the package.

Go to solution explorer and click on deploy

Click next on introduction screen you will see below screen which is asking you to choose the path of project. Select project deployment file if you have already. Now we are doing from ssis package we need to choose project deployment file

Click on next mention the server name where do you want to deploy. Select integration service catalog folder which you already created in earlier steps and click ok.

Click next here you will see the review information and click on deploy.

Done check SQL Server you will see below type structure which contains deployed package.