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.

Creating the SSISDB Catalogs in SSIS 2012

For deploying the SSIS package in 2012 first task is to create the SSISDB catalogs and database. Begin by opening SSMS and navigating to the Integration Services Catalogs folder in Object Explorer.

Right click on the Integration Service Catalogs and select Create Catalogs.

The Create Catalogs dialog box will appear, showing us that the name of the database will be SSISDB. (We can't change the name.) In the dialog box, specify the password to be used by the encryption mechanism for this database and select the Enable CLR Integration check box.

 If desired, we can also select the Enable automatic execution of Integration Services stored procedure at SQL Server startup check box.

When we click OK, We will see that the SSISDB catalogs is now available in the Integration Services Catalogs folder in Object Explorer.

When SSISDB created on the Integration Service Catalogs a Database in created on Database named as SSISBD.

It has several tables to manage the Package. List of tables

catalog_encryption_keys catalog_properties data_type_mapping environment_permissions environment_references environment_variables environments event_message_context event_messages executable_statistics executables execution_component_phases execution_data_statistics execution_data_taps execution_parameter_values execution_property_override_values executions extended_operation_info folder_permissions folders object_parameters object_versions operation_messages operation_os_sys_info operation_permissions operations packages project_permissions projects trace_xe_action_map trace_xe_event_map validations

The SSISDB catalog has a specific structure for organizing packages. We group individual SSIS packages into a project, and we put a group of related projects into a folder. The folder will be directly under the SSISDB catalog.

Difference between Package and Project Deployment Model

Below is the deference between package and project deployment model

Project deployment model	Package deployment model
Project is deployed	Package is deployed
Project uses parameters	Package uses Configurations files/tables
Project is located in an .ispac file and packages have .dtsx extensions	Packages have .dtsx extensions.
Project is deployed to integration service catalog.	Packages are deployed to the MSDB or File.
CLR Integration is required.	CLR integration is not required.
New environments in the SSIS catalog can be used with parameters.	System environment variables can be used with configuration.
Projects and packages can be validated before execution with T-SQL or Managed code.	Packages are validated just before execution and can be validated with dtexce or managed code.
Packages are executed with T-SQL. Parameters and environments can be set with T-SQL.	Packages are executed with dtexe and dtexecui. Command parameters can be passed to the command prompt.
Robust logging is built in with several report.	Logging is built in with no report.

Package Parameters and Project parameters in SSIS

A parameter, like a variable, is a placeholder that has a name, data type, scope, and value.SQL 2012 introduced a new parameter paradigm. Whit the help of Parameters we are able to pass in new values for a specific package execution. When our packages are deployed to the new SSIS , an interface is provided to enable us to change the values of parameters prior to running the package. Parameters are very similar to variables, except parameters are easier to change and configure using the new SQL Server Management Studio interface for SSIS.

Parameters also have a scope. Parameters can have a package scope or a project scope. A parameter with a project scope can be used in all packages within the project. Package scoped parameters can only be used within the package in which they are defined.

There are two types of parameters

Ø  Package Parameters

Ø  Project parameters

Package Parameters

Package parameters are new with SSIS 2012. The scope of a package parameter is the individual package where it resides. We can to use a package parameter when, at run-time, the value is different for each package. 

Here I am creating a simple package where I am importing the data from a table and storing that in the flat file.

Taking Data Flow Task

  

Double click on the Data flow task

In Data flow I am taking the Source as OLEDB. Now I am Configuring the required configuration.

  

Now I am taking the flat file to store the result.

  

I am creating variable to store the file name and location. Right click on the Flat File Connection Manager and click on the Properties.

Now I am creating the Configuration for flat file.

Now Click on the Expression

  

Select Properties as connection string

Click ok.

Now package is ready to run.

Before executing the package

There is no file in this folder.

Now I am executing the package.

Package executed successfully.

 This package is created on the Dev Environment. Suppose I we want to move this package to QA, CERT and finally Prod Environment. Then we need to change the configuration. In SSIS we can create the Different Environment.

Creating Different Environment in SSIS

By default package is in development Environment.

Suppose we want to create different Environment then we need to click on the Drop down.

Click on the Configuration Manager.

Click on the New

Write the Environment name. If we want to copy the setting of any environment then select the Environment. And Click ok.

I have created four environments.

Now I am creating the parameters.

Click on the parameters Tab.

Click on the new variable. and we need to configure the value of each environment.  

  

Click on the Add parameters to configuration tab.

Click on the Add button.

Select the file path. And click ok.

Here we are configuring the file path value according to the environment. Click ok.

Keep in Mind the configuration are applied only when packages are executed in Visual studio.

New right click on the Flat file configuration.

See the Parameter is start with $Package:: variable name.

Click ok.

Before executing the package.

  

Executing the package in dev

See the result

  

See now I am changing the environment. Running it on QA

See the result.

Similarly we can run on the all environments.

Project parameters

Project Level Parameters are available to all the packages in the project.

Click on the Project.Params

Here we can add the project level parameter

Adding a new variable

Now we need to add this on all environments.

Click on that

 

Add Project file path.

Click ok. New we need to configure the connection string value of flat file destination.

New we can execute the package.

Package executed successfully and gets the expected result.

See here now I am creating a new package and let’s see either we can use the

 

For checking the project level parameter I am taking Expression task

 

Double click on the Expression task.

 

See in Expression builder we are able the see only project label parameters.