SSIS Project Deployment Model in SQL Server 2012

After creating the package in SSDT we need to deploy it on server. Let’s see how we deploy the package in server.

Already I have created a package in that I used package parameter: Read here.

http://bageshkumarbagi-msbi.blogspot.in/2016/07/package-parameters-and-project.html

If we are deploying the package first time we need to create the SSISDB catalog on the server.

Read here: How to create the SSISDB catalog

http://bageshkumarbagi-msbi.blogspot.in/2016/07/creating-ssisdb-catalogs-in-ssis-2012.html

First we need to build the package. Right click on the Project and click on the Build

It compiles the whole package.

After that again right click on the project and select the Deploy

Click on deploy. Integration services deployment wizard will be open.

Click next.

Write the server name and click ok.

Now we need the give the Folder path. Click on browse. Create a new folder in SSISDB.

Write the package name and description (optional). Click ok.

Select the folder and click ok.

Now click on Next.

We will see the Review wizard.

Click on deploy

Now package is deployed on server. Close the wizard.

Open the server and navigate the Integration service catalog.

I have deployed my package on the MyFirstProjectDeployment folder. See in this folder there are two folders are created.

Ø  Projects

Ø  Environments

Package

In this folder .dtsx packages are store.

Environments

Environments are container that store parameter to be used in our package deployed to an SSIS catalog. It can be used when we executing packages on the server to inject parameter value into a package. This is especially useful when our package need to be executed against different environment like dev, QA or Prod.

I have create a package in that I have created three environment (Prod, Dev and QA): Read here

http://bageshkumarbagi-msbi.blogspot.in/2016/07/package-parameters-and-project.html

How to create Environments

Right click on the Environments folder

Click on the Create Environment

Create Environment Wizard will be open.

Write the Environment name and description and click ok.

Prod environment is created.

Now we need to configure the variables

Right click on the Prod Environment

Click on the Properties.

Create the variable

I have create the variable “FilePath” and assigned the vales. If you want to give the permissions to the environment then click and Permission tab and give the permission.

Now click ok.

Here I am creating three Environments.

All Environments are created. Now we need to configure them in package.

Right click on the project and select the Configuration

Configuration wizard will be open

Select the scope of the configuration

Now we need to select References

Click Add

Select the Environment and click ok.

Adding all environment

Click ok.

Now we need to assign the values

  

Click on the Parameters.

Click ok.

See the value

Click ok.

Now we are ready to run the package on different environment.

Before executing the package in Dev environment.

Now I am executing package in dev environment

Right click on the package and Click on the Execute

Select the environment

Click ok.

Click Yes. It will show the execution report.

Package executed successfully.

See the Dev folder file is created.

Similarly we can execute this package in all environments.