Microsoft SQL

Project Deployment in SQL Server Integration Services 2012

SQL Server Integration Services (SSIS) is the ETL (Extract, Transformation and Load) tool in the Microsoft Business Intelligence suite. Microsoft changed the way they handle data loads completely with the introduction of SQL Server 2005, offering complete ETL functionality as opposed to mainly Extract and Load functionality available in earlier versions. The release of SQL Server 2012 introduced another significant improvement on previous versions, providing a new way of configuring packages for deployment. Once development of packages has been completed, the challenge is to deploy those packages from a development environment to test or production environment without having to manually reconfigure them for the new environment. Configuring packages in SSIS has not always been very easy. A main problem was the different possibilities that developers had, including XML configuration files, Environment variables, Registry entries, Parent package variables and SQL Server configurations.

In SSIS 2012 the configuration management has been redesigned and now there is one common way to do this. In SSIS 2012 parameters on project and package level have been introduced, as well as environments in the SSIS catalog and together with parameters it offers a new opportunity to configure packages during runtime.

There now exists two different deployment models to help deploy SSIS packages and projects to different environments such as development, test and production. The two deployment models are the project deployment model and the older package deployment model. This article describes how to perform project deployment on SSIS packages in SQL Server 2012.

Project deployment model

Once package have been developed and tested it needs to be deployed to an environment where it will be used, this can be either development, testing or production.

In SSIS the configuration manager is used to create the different environments. To open the configuration manager either select it from the drop down list on the main toolbar next to the Start Debugging icon

or right click on the project and select Properties.

To open the Configuration Manager just select Configuration Manager... in the top right corner. To create a new configuration scenario select <New...> from the Active solution configuration drop down list.

Provide a name for the configuration and either copy settings from an existing configuration or create new configuration settings. Click on Close to return to the Project Deployment model.

Usually the Server Name and Server Project Path differ between environments so by setting those values for the different environments it is possible to deploy the package to different systems only by choosing a value from the drop down list.

Now in combination with parameters the project deployment model gets more useful as it is possible to assign different values to parameters for each scenario, for example you can assign different connection strings to each of the development, test and production environments.  It provides the opportunity to execute packages against different systems during design time simply by choosing a different scenario form the drop down list, and also the possibility to deploy the project and packages with the correct configurations for the different environments.

Parameters are new to SSIS with SQL Server 2012 and can be created on project or package level. If defined on project level then it can be used in all packages within that project and if it is created on package level then it can only be accessed within that specific package. Parameters can't store results from Execute SQL Tasks but other than that works similar to variables.

To create project level parameters double click on Project.params in the Solution Explorer window.

Click on the Add Parameter button to create a new parameter and set the following properties:

  • Name - Provide a descriptive name for the parameter.
  • Data Type - Specify the data type of the parameter.
  • Value - The default value of the parameter during design time.
  • Sensitive - If a parameter is flagged as sensitive then it's value will be encrypted when the project is deployed.
  • Required - Indicates that it is mandatory to pass a value to this parameter before the package can be executed.
  • Description - Provide a description of the purpose of the parameter.

Similarly to create package level parameters just click on the Parameters tab on the Package design window. New parameters are created in the same way and the same properties should be set.

If different scenarios have been applied to the project then it is possible to assign different values to a parameter for each scenario. This can be done by clicking the Add Parameters to Configurations button.

Click on Add and select the parameter you want to configure for the different scenarios.

Change the values for each scenario and click OK when finished. here you can for example set the connection string or server name or file path for the different servers.

Now that the environments have been created and the parameters have been created and configured for the different environments the integration services catalog needs to be created on the server in order to deploy packages to the server.

The catalog is used to store, manage and monitor deployed packages. The catalog uses CLR stored procedures so CLR integration needs to be enabled. After CLR is enabled you can use SQL Server Management Studio Object Explorer to create the catalog. Just right click the Integration Services Catalogs and select Create Catalog...

You will be asked to supply a password which is used to create a database master key used to encrypt sensitive data in the catalog.

After creation the catalog is displayed twice in the object explorer, first in the databases node and also in the Integration Services node. In the database node you can work with the catalog SSISDB the same as with any other user database. In the integration services node you can administer the catalog by right clicking on the child nodes.

The catalog stores all environments which will be used to configure packages during runtime. Each folder created in the SSIS catalog contains two subfolders - Projects folder where SSIS packages will be deployed and stored and the Environments folder which manages the different environments, created for specific projects.

After the SSIS catalog has been created and configured and at least one custom folder has been created to store projects and packages, then environments can be created in the appropriate subfolder. Each custom folder can store different projects and environments so it is possible to reuse already defined environments and also to define more than one environment per project.

To create a new environment simply right click the subfolder and select Create Environment... and then specify a name and description for the environment. After clicking OK a new entry appears in the Environments folder.

To configure the newly created environment just double click to open it. Three nodes exist on the left pane - General, Variables and Permissions.

The General node displays the name, identifier and description of the environment.

Click on the Variables node to create variables for the environment. Specify the variable name, type, description, value and also indicate whether or not the information should be flagged as sensitive.

Information regarding environments can be retrieved from the SSISDB, stored in the following four tables in SQL Server:

  • internal.environments:Stores unique id, name and folder in which the environment is located.
  • internal.environment_variables:Provides information about variables used in environments, such as name, data type and current value.
  • internal.environment_references:Stores the relationship between projects and environments. It gives information on which project references which environment.
  • internal.environment_permissions:Stores information about permissions for each environment.

These tables also provide the possibility to modify existing environments or create new ones.

After a project has been deployed to the SSIS catalog it is possible to configure it by right clicking the project or the package entry and selecting Configure...

This opens up the window to configure the deployed SSIS package. All parameters and connection managers can be configured on both project and package scope.

For parameters the following information is provided:

  • Container - Parameter is either on project or package level.
  • Name - Parameter name.
  • Value - Current value of the parameter.

For connection managers the following information is provided:

  • Connection String - The connection string of the connection manager.
  • Initial Catalog and sever name- Values of server and initial catalog.
  • Username and Password - For authentication purposes.
  • RetainSameConnection - Specify if all tasks should use the same connection.

Under the References node it is possible to assign environments to the project, which can be used to configure the values of the different properties.

To assign the environment variables to parameters or connection manager properties click the ... button next to each value.

In the Set Parameter Value window choose between editing the value manually, using the default value from the package or assigning a variable from the environments.

For each parameter that has to be configurable, one variable needs to be created in the according environments.

If more than one environment is used within a project and each should configure the same parameters and connection managers then it is mandatory that the variable names are the same for all environments.

When development of the SSIS package and project has been completed we can deploy the project to the catalog that was created. When using the project deployment model, selecting deploy will immediately launch the Integration Services Deployment Wizard. In the wizard you can choose to deploy the current project or import an existing project from another server, for example you want to move your project from development to test server.

After this you select the server where you want to deploy to and also the location (folder) in the SSIS catalog.

It is important to note that you can only deploy the entire project and not individual packages within a project.

ETL with Microsoft SSIS: First steps and common issues

First impression when using Microsoft SQL Server Integration Services (SSIS), the developer’s first impression is the tool provides powerful features in addition is simple to use. Although this is true, there are some common errors that can make us waste our time. The purpose of this blog article is to cover some of these very common issues and save some time when starting to develop a solution with Microsoft SSIS.

As a quick overview, SSIS is a component of Microsoft SQL Server that we can use to perform data integration tasks. Microsoft provides a visual tool for the ETL Development based on Visual Studio called SQL Server Business Intelligence Development Studio.

Components:

  • Solution Explorer: Contains all the project files, mainly: Packages, Folders and Project configuration managers. The configuration managers are just configuration files for providing connectivity to sources (flat files, databases…). There are two types depending on the ownership, package and project connection managers.
  • Properties tab: Displays properties of the selected item.
  • Connection managers: Here we have visibility for all the connection managers used by the package (project and package connection managers).

When taking the first steps with the tool, there are some common errors that can make us waste a lot of time and that can become a challenge once we deploy the solution in a server. We can summarize these problems into the following points:

  • Configuration of 32/64 environment
  • Protection Levels
  • Network drives

Configuring for 32/64 environment:

The machine may not have 64 bit for Microsoft Excel, Oracle or ODBC data provider. In order to configure the project to be executed in 32-bit mode, we have to configure it through the project properties. If we go to the Solution Explorer view, click on properties->debugging, we can switch the 64-bit mode:

Next step after your development is done is probably to deploy the ETL in a server. The project is deployed in an Integration Services Catalog in an instance of SQL-Server. The SQL Agent will be then responsible for executing and scheduling the packages. All this process is detailed described in the following link:

http://technet.microsoft.com/en-us/library/hh231102.aspx

Once deployed in the server, we can also test whether this is going to be executed in 32 or 64 bit (in the weird case the server is using a 32-bitruntime). We can switch the 32 or 64 mode if we click in the job->properties->steps->step property-> 32-bit runtime.

Protection levels

An SSIS project contains confidential data for many purposes. Most probably contains connection manager files in order to be able to connect to different data sources. Within theses files, SSIS stores the credentials for the data source. These credentials can be configured to be encrypted, depending on the protection level. SSIS provides the following options for protection level:

  • DoNotUseSensitive: Does not store any info
  • EncryptAllWithPassword / Encrypt Sensitive with password
  • EncryptAllWithUserKey / EncryptSensitiveWithUserKey
  • Server Storage

More details about the protection levels can be found here: http://blogs.prodata.ie/post/SSIS-Package-Protection-Levels-and-their-Usage.aspx

Usually there are not many problems when developing in your machine. Many problems come up once the package is deployed and the SQL Agent tries to read out data from them; if you debug this process you will get a connection attempt error. Switching between these options can help us when troubleshooting.

However, most common problems come from one of those reasons:

  • Protection level is different at project level and package level.
  • Protection level is configured as encrypt with user key and SQL Agent user is different from the project owner.

We can change the package protection level by going to the control flow and in the Properties tab (down right corner in the image below).

Whereas the protection level at project level can be found by right click Project-> Common Properties-> Security -> Protection Level.

Following the same procedure, we can see the username who created the package/project. If encryption mode is enabled (by default it is) then we need to make sure that the project/packages ownership matches the username in the SQL-Server Agent Service. We can access this information by clicking Control PanelSystem and SecurityAdministrative ToolsServices and then properties of the SQL Agent service->Log On

Network drives

A common issue when loading data from flat files appears when these files are located in a shared drive.  SQL Agent will not recognize network paths if they are not in UNC format (read more about the UNC in http://compnetworking.about.com/od/windowsnetworking/g/unc-name.htm ), whereas SSIS works fine with them.  It may be confusing since when the developer deploys the package in the server everything seems to be fine. But at the time the job executes the SQL agent will report: Path not found error.

Since we can not specify the UNC path with the SSIS GUI, we need to write it within the connection manager XML file and manually introduce the UNC path in the DTS Connection String:

<?xml version="1.0"?>

<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"

DTS:ObjectName="Web Portal SOURCE"

DTS:DTSID="{071F5E66-BE6F-4BA6-A8D7-477A75185BB3}"

DTS:CreationName="FILE">

<DTS:ObjectData>

<DTS:ConnectionManager

DTS:ConnectionString="dc01fpnv04public$My Number My IdentityExclusionsWeb-portal dataWeb portal weekly Submission Report new1.csv" />

</DTS:ObjectData>

</DTS:ConnectionManager>

We can access this file by right click in the connection manager -> View Code:

EspañolEnglish