THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Business Intelligence consultant from the Netherlands

SSIS Denali CTP3 – What’s new?

Last week Microsoft released CTP3 of SQL Server Integration Services (SSIS), code name: Denali. In this blog post I will look into the new key features and some of the minor improvements in SSIS Denali.

1. Development

Shared Connection Managers
Connection Managers can now be shared on SSIS project level.
You can create them in the solution explorer, in the folder Connection Managers:
clip_image001

Once created, they will appear automatically in all your SSIS packages. The names are in bold so you can recognize them between your package-level connection managers:
clip_image002

You can also create Shared Cache Connection Managers for your cached lookups. This will be very useful for lookups that are performed multiple times, for example when you look up dimension tables multiple times from your fact table ETL. You can just convert the local cache connection manager by right clicking it:
clip_image003

What happened to the Shared Data Sources we knew from prior SSIS versions? Well they are no more. Shared Data Sources only lived at design time and not at runtime so they were not really useful anyway. Shared Connection Managers do live at runtime and offer even more possibilities as I will show later on, so they replace the old Shared Data Sources.

Data Flow - Column mappings
SSIS always mapped columns from source to transformations or destinations with the help of lineage ids. Every column had a unique metadata ID that was known by all components in the data flow. If something changed in the source this would break the lineage ids and raised error messages like: The external metadata column collection is out of synchronization with the data source columns.
To fix this error you would re-map all broken lineage ids with the “Restore Invalid Column References Editor”.
In Denali lineage-ids are no longer used. Mappings are done on column names, which is great because you can now use auto map on column names and even copy/paste pieces of another data flow and connect them by mapping the corresponding column names.

Data Flow - Flexible order of authoring
This improvement helps you edit data flow components even when they don’t have an input attached. Theoretically you can build your data flow backwards; start with the destination and track back to the source.

Data Flow - Groupings
You can now group data flow components. Select the components you wish to group, right click and select Group:
clip_image004 


The result is some sort of a data flow sequence container:
clip_image005

By clicking the arrow it will collapse:
clip_image006

Data flow groups are 100% eye candy; you can’t set any properties on them.

Data Flow - Data Quality Services Cleansing transformation
With this transformation you can apply data quality rules in the data flow. This is done by using a Knowledge Base which can be created by yourself or downloaded from the Windows Azure Marketplace. For example you could apply a rule that checks if a given postal code column is valid for a particular town column in your record.

Data Flow - Data Tap
In Denali, we have the possibility to attach a “tap” at a data flow path (arrow). This tap captures all data coming through and dumps it in CSV files.

2. Configuration

SSIS Denali is fundamentally different to its predecessors when it comes to the concept and usage of configurations. SSIS package configurations are obsolete (they will still be available if you really want to use them ;-)) and parameters and environments are in the new kids in town.

Parameters
SSIS Parameters look a lot like SSIS variables but there are some differences. There are two types of these parameters:
1. Package Parameters:
Look at Package parameters as C# parameters, which are passed as input to a C# function(=your package). You can set them when executing (call) a package and the lifecycle of the parameters are limited to the SSIS package itself.

The difference with SSIS variables? You can set the parameters while executing a package with SSMS or an Execute Package Task.

Define your Package Parameters at the Package Parameters tab:
clip_image007

2. Project Parameters:
Identical to Package Parameters, except for the scope, these Parameters exist on project level and can be referenced throughout all SSIS packages in your project. Package Parameters only live inside a single SSIS package.

You can define Project Parameters in the solution explorer within your SSIS project:
clip_image008

Both Package and Project Parameters can be referenced from your SSIS packages, you recognize them by their prefix, $Package or $Project:
clip_image009

Setting parameters in a Execute Package Task is achieved by the new Parameter bindings tab:
clip_image010

It’s also possible to parameterize SSIS tasks on the Control Flow by right clicking them and choose Parameterize:
clip_image011

Loads of capabilities here! I now realize that I have created a workaround for Package Parameters with my SSIS Package design pattern for loading a data warehouse where I (mis)used Package Configurations as Package Parameters. Creating a new package design pattern for Denali definitely goes on my TODO list!

Environments
Environments are a collection of SSIS package settings that can be define on the SSIS Server. At runtime, the environment will override these settings in the SSIS packages. You can create multiple environments and when you run your SSIS packages you can select which environment it should use. It’s also possible to let multiple SSIS projects run under one environment, so flexibility all around the clock.

To make you understand the principle of Environments right away I have created a simple example that you will commonly use: Create two Environments, one with development settings and one with production settings.

I have deployed a Test SSIS project to SSMS which contains one SSIS package with one Shared Connection Manager. Notice the new Integration Services folder structure:
clip_image012

Next right click Environments and choose Create Environment:
clip_image013

Type Development as name for the Environment and click OK:
clip_image014

Now double click the Development Environment:
clip_image015

Click on the Variables tab and create an Environment Variable which will overwrite the Shared Connection Manager. Type in the connection string for the development server as the Value and click OK:
clip_image017

Next create another Environment with the name Production and also create an Environment Variable with the name SCM_EnvironmentVar. The only difference between these two variables should be the value of the variable; it contains the production server connection string instead of the development value.
You now have two Environments with one Environment Variable for the Shared Connection Manager each:
clip_image018

We now need to reference the Development and Production Environments in the Test project so they can be used. Right click the Test project and choose Configure:
clip_image019

Go to the references page and click Add..
clip_image021

Add both Environments:
clip_image023

Both environment are now referenced from the Test SSIS project and are available. Let’s configure the Shared Connection Manager so it will use the value specified in one of the environments. Click on the Parameters page, the Connection Managers tab and the next to the ConnectionString property of the Shared Connection Manager:
clip_image025

Now select Use Environment Variable and select SCM_EnvironmentVar. Click OK:
clip_image026

The name of our Environment Variable is shown in the Value box of the ConnectionString property:
clip_image028

We have now succesfully attached the Environment Variable to override the ConnectionString property with a value from either the Development or the Production Environment. Executing the package under one of both Environments is very easy. To do so, right click the package and choose Run:
clip_image029

The Run Package dialog box appears and you instantly see a message that reminds you to assign a value to the ConnectionString property of connection manager SharedConnectionManager:
clip_image030

At the bottom of the dialog box you can select the Environment your package should use. Select the .\Development Environment and click OK:
clip_image031

In the overview report you see the package has succesfully ran under the Development Environment:
clip_image032

If you change the package to use the Production Environment and you run the package again, you’ll get the following execution information:
clip_image033

Pretty straightforward and easy to use. The big difference? All the configuration work has been done in SQL Server Management Studio! Exactly the place where it should be done. In the current SSIS versions most of this work was done within Visual Studio and the configuration was done by editing tables. So unfriendly! Big improvement, great work MS!
In this example I have set a property of a Shared Connection Manager but of course it is possible to set Package or Project Parameters with Environment Variables.

3. Deployment

We have had the option to deploy SSIS packages from Visual Studio for a long time thanks to BIDS Helper. Microsoft finally added this functionality out of the box in Denali. They even did more: you can now also build your projects from within Visual Studio!

There are two ways to deploy (push) your projects to the new SSIS server, directly from Visual Studio or by using an Integration Services Project Deployment File. They both use the new Integration Services Deployment Wizard.
Deployments directly from Visual Studio can be done by right clicking your project and then choose Deploy. Now, the Deployment Wizard will pop straight up:
clip_image034

The other way is building your project first. This was already possible in SSIS version(s) prior to Denali but did not really have useful meanings.

In Denali this is different. Building your project will generate an .ispac file which contains your entire project and this can be used by the Deployment Wizard. Again a very nice new feature.

Handing over a new release of a SSIS solution to a customer’s IT department for deployment can now be done in a very nice, professional, manner. Ispac files can also be opened by Visual Studio to import your SSIS project.
Building your project can be done by right clicking your project and choosing Build:
clip_image035

The output window displays the following:
clip_image036

When looking at the bin\Development folder we see the new Test.aspac file:
clip_image037

clip_image038

Double clicking (or choosing Deploy in Visual Studio) this file will start the new Integration Services Deployment Wizard:

You can now select the project that you wish to deploy. You can either select to use the .ispac Project deployment file or choose to deploy an existing project located in any Integration Services catalog. The last option is useful when you wish to deploy from, for example, a TEST SSIS solution that is already located in a catalog to an ACCEPTATION environment in another catalog.
clip_image039

The project is loaded and validated:
clip_image040

Next, you now need to select the destination, which has to be a SSIS Server:
clip_image041

Review your selections and click on Deploy to start the actual deployment:
clip_image042

The results show a successful deployment. Notice the protection level has been changed. What happened here?
The wizard clears the protection level, as we know from Visual Studio, to prevent “cannot decrypt password” errors.

Instead of using protection levels, passwords will be stored in clear text. The entire package (with the passwords in it) will be stored encrypted in the SSIS Server tables you are deploying to.
clip_image043

The project has now been deployed to the server:
clip_image044

When you right click the Test project and choose for Versions you are able to see the current version of your project:
clip_image046

If you deploy a newer version later on, the existing version(s) stay on the server and you can easily role back to a previous version if you’ve made a bad deployment.

The diagram below shows the entire deployment life cycle (source: TechNet):
clip_image048

4. Management

The new SSIS Server is the central storage and administration point of your SSIS solutions. No longer is this a standalone server that you’ll need to manage. Basically it is a database with Stored Procedures.

You now have an Integration Services node available in SSMS when you connect to the database engine:
clip_image049

Under the Integration Services node you will find your SSISDB catalog which holds all your SSIS solutions with its packages, environments, etc. The physical storage of these objects will be conducted in a SQL Server database with the same name as the catalog:
clip_image050

This database also contains all the stored procedures containing all the programming code for the SSIS Server:
clip_image051

With the help of these stored procedures you can manage your SSIS Server: e.g. your parameter values, connection managers, and override properties by using environments.
Next to the configuration functionalities I have discussed earlier you can also implement security to control access to the catalog, both on folder level and package level. When you right click a folder/package in the catalog and choose the properties you’ll get the following window where you can manage security access:
clip_image052

Finally you’ll get an out of the box reporting dashboard which is built on the out of the box logging functionality that SSIS offers. On this dashboard you’ll get information about the execution history of your package and its sub packages, view which parameters were used, view specific messages and get a performance report over time.
All the information is logged automatically if any package runs on the server. The information is very detailed; you can even get the row counts between SSIS data flow transformations!

When running a package you are able to select a logging level on the Advanced tab:
clip_image053
You can choose for:
- None: turn logging of for performance reasons
- Basic: error and warning logging
- Performance: detailed trace information
- Verbose: diagnostics and fault debugging

When I run my Test package I’ll get the following execution dashboard. There are some hyperlinks that navigate to more detailed reports.
clip_image055

So no need for creating your own logging framework anymore, it’s all out of the box!

Published Friday, July 22, 2011 7:44 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Venugopal said:

Really great option, and explanation. thanks

July 25, 2011 1:16 AM
 

Elmozamil Elamir said:

Thanks for your explanation

July 25, 2011 8:50 AM
 

Perry Wiggers said:

Good and compact overview of relevant and usefull changes/additions to BI-Denali!

Thanx.

August 8, 2011 5:33 AM
 

Piyush Bajaj said:

Hey its a nice one.

I have posted an article on the topic "Installation Guide for Denali CTP 3",

So you can browse it from here:

http://www.sqlservergeeks.com/articles/sql-server-bi/56/sql-server-installation-guide-for-denali-ctp-3

August 18, 2011 2:57 AM
 

kanth said:

Thanks for your explanation

January 25, 2012 12:52 PM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement