SSIS enhancements in Denali CTP3

The third Community Technology Preview (CTP3) of SQL Server code-named Denali is upon us and, although you would never guess from the official announcement, there is a whole raft of enhancements to SQL Server Integration Services (SSIS) and in this blog post I will take you on a tour of some of them. I must stress that nothing here is finalised and anything is liable to be changed prior to the full release of Denali. Wanna know what’s new? Read on…

Parameterize a Task

If you have experienced CTP1 then you will know that one of the big new features in Denali is Parameters (if you know nothing of Parameters then you may want to take a read of my earlier post Parameters in SSIS in Denali from November 2010). In CTP3 there is a new designer enhancement that makes it a lot easier to work with parameters – right-clicking on a task shows a “Parameterize…” option:

Clicking that launches the Parameterize dialog:

In the screenshot above I am parameterizing the WorkingDirectory property of an Execute Process Task. From this dialog we have the option to:

Create a new parameter

Use an existing parameter

Initialise a newly created parameter with some value

Define a newly created parameter as package or project scoped

Define a newly created parameter as required or not

Once the parameter has been specified an expression will be placed onto the selected property setting it to the value of the parameter:

Package Parameters Tab

Package-scoped Parameters now have their own tab within the designer whereas in CTP1 they were shoehorned into the Variables pane:

Shared Connection Managers

This is one of the big-ticket features in CTP3. Connection Managers are no longer confined to a package, they can live as part of the project and be used by multiple packages. They will appear alongside package-scoped Connection Managers in the familiar Connection Manager tray in *all* packages within that project. Currently the visual differentiation between a package-scoped and a project-scoped Connection Manager is that the project-scoped Connection Manager appears in bold text:

A project-scoped Connection Manager can be used wherever you you can use a package-scoped Connection Manager.

When a project containing a Shared Connection Manager is deployed to the server then any property of that Shared Connection Manager can be changed just like a Project Parameter can be.

The last related point here is to note that the Data Sources and Data Source Views folders that appeared in Solution Explorer in SSIS2008R2:

have disappeared, and I am sure they will not be missed.

Project Parameters node in Solution Explorer

Project Parameters now have their own node in Solution Explorer rather than being hidden underneath a right-click menu like they were in CTP1.

Expression Indicator

Variables, Connection Managers and Tasks now have an fx adorner applied to them indicating that there is at least one expression on that object (yes, just like what BIDS Helper does for earlier versions).

Change variable scope

One of the biggest annoyances in the previous SSIS Designer was that the scope of a variable could not be changed. Not anymore, in Denali it is possible to change the scope of a variable:

Double-click to add a task

Its not really possible to demo this one with a screenshot but its pretty easy to explain. Double-clicking on a task in the toolbox will add it to the container that currently has the focus (which may of course be the package). This works for components in the data flow too.

Sort by name

It is now possible to sort packages alphabetically in Solution Explorer:

Note how the position of “Package.dtsx” & “Another Package.dtsx” has been reversed.

Simplified Data Viewers

Have you ever got annoyed that adding a data viewer takes far too many clicks given that you do the same thing (Add->Grid->Grid Tab->OK) every time? In Denali its a lot easier; there is only one option – Grid (did you every use anything else anyway?) and all columns are automatically selected:

Different style of Success/Failure Indicator

In SSIS2008R2:

And now in Denali:

A little more understated in Denali I think you’ll agree although I suspect some people will prefer the old way! I am undecided. Arguably an icon is better than a change in colour for those that suffer from colour-blindness.

Load files with multiple row formats

One of the big complaints about SSIS over the past six years is that it has poor support for loading files where rows can have variable numbers of columns. The typical way of dealing with this was to use the Ragged Right feature and parse out the columns in a Derived Column component however that is no longer necessary – SSIS can now parse all of the columns from such files in the Flat File Source Adapter. Here is one such file that has differing row formats:

It is a simplistic example of a file that contains both OrderHeader and OrderDetails records. The OrderHeader records consist of an OrderId and a Name, the OrderDetails records consist of an OrderId, an OrderLineNumber, a ProductName and a Quantity. In the Preview screen of the Flat File Connection Manager we can see that SSIS is able to parse both of these row formats:

Typically you could then use a Conditional Split component to split the dataset into Header and Detail records:

Script out from the GUI now wired up

In earlier CTPs the Script button on the various GUIs in the SSIS Catalog didn’t do anything

In CTP3 however these are now wired up correctly so hitting CTRL+Shift+N will (in this example) produce a script containing the code required to execute a package:

Logging Level

In Denali logging is no longer configured within a package, it is done on the SSIS Server (much more on this in an upcoming blog post). This much we already knew (learn more at SSIS Server, Catalogs, Environments & Environment Variables in SSIS in Denali) however in CTP3 we now have the option to choose what data gets logged. There are four options here; None, Basic, Performance & Verbose

I won’t cover each choice here as there will be plenty of documentation available around this, for now just know that the option is available.

Data Taps

Data Taps are an exciting new feature coming in Denali and have taken me completely by surprise. Ever wanted to to view the data in an executing package like you can using a data viewer in BIDS? That is what data taps provide. You don’t have to build them into your package either, they are added on the server when the package is executed.

Data taps are worthy of a post of their own so I’ll cover them separately if no-one else does so first.

Expression Task

I once wrote a blog post entitled Nesting variables to calculate values where I opined that (where possible) it was better to build variable values dynamically using expressions rather than assign a value using a Script Task because this resulted in less executables in your package. Some people in the comments disagreed with me partly because they liked the explicitness of a task to do this job and for those people there is a new task in Denali that is right up their street – the Expression Task. Put simply the Expression task will assign the result of an expression to a variable, in the example below I am assigning a value to a variable called “Sum”

I have to be honest and say I despise this task. The variable being assigned to should be available in a dropdown box, you shouldn’t have to type it. Furthermore if they are not going to provide the variable in a dropdown then the box in which you type should not be labelled “Expression”, it should be labelled “Assignment” – because that is what it is. I will not be going anywhere near this thing, will you?

On the plus side they have moved the system variables into a “System variables” node in the “Variables and Parameters” tree which is a most welcome change.

Wrap-up

I haven’t covered everything that’s new in CTP3 but this is the bulk of it from a pure development perspective. What out of that little lot most excites you? Let me know in the comments.

I’ll touch on some other things in upcoming blog posts including an enhancement which is seemingly very insignificant but which actually excites me more than anything I’ve talked about here. Watch this space.

Comment Notification

Comments

Good summary. I am looking forward to your more detailed posts coming in the future. Didn't know or remember about Data Taps and that is a welcome change. On the fence on the variable assignment through an expression task. Would like to see multiple assigns and variables supported here instead of just one line - assuming you want to assign multiple variables this way. The "Multiple Row Format" enhancement is also a welcome change. I had to deal with these for a long time in both SSIS and DTS - it was always painful, especially when hoping you could just define all columns and deal with missing ones, especially Excel exported as CSV. I remember finding it easier to just open the CSV in Excel, add a new dummy column, then re-save as CSV. That would place all of the missing "columns" back in the file so it would import.

I'm very pleased to see a lot of handy BIDSHelper functionality is now in the out-of-the-box product.

The ability to load a file with multiple row formats is also great! (this will make answering questions on the MSDN SSIS forum a lot easer :)

And finally, administration of SSIS has become much more powerful. Especially that you can now build an entire SSRS report fleet on your SSIS logging.(can't wait to see your updated SSRS reports on that Jamie)

I think the removal of a 4000 character limit on an expression is great. There are times (for example, building complex SQL statements dynamically) when this approach had to be abandoned and a script task used instead, which means another task, and more coding.

And support for files with multiple row formats - a good improvement.

Also looking forward to shared connection managers - very convenient.

I was quite happy with the red/green colour-coded success/fail status however, lucky I'm not colour blind I guess :-)

Actually there IS going to be a way to assign values to properties, I just haven't explored it much. I believe its there as a failsafe in case you need to override stuff without redeploying - will look into that later.