THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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:

image

Clicking that launches the Parameterize dialog:

image

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:

image

Package Parameters Tab

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

image

Shared Connection Managers

image

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:

image

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.

image

In the screenshot immediately above you can see that I am setting the ConnectionString property of a Shared Connection Manager to the value of an environment variable called ConnStr (for more on environments and environment variables go take a read of SSIS Server, Catalogs, Environments, Environment Variables in SSIS in Denali).

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

image

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

Project Parameters node in Solution Explorer

image

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

image

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:

image

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:

image

image

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:

image

4000 character limit on expressions has gone way

This one is pretty significant. In SSIS2008 an expression of REPLICATE(“a”,4001) times would result in design-time warnings and execution-time errors:

image

In Denali the 4000 character limit has gone away.

New expression language functions

We have three new functions in the expression language:

image

  • LEFT(<string>, <number-of-chars>) – A shorthand way of writing SUBSTRING(<string>, 1, <number-of-chars>)
  • TOKEN(<string>, <delimiter>, N) – Returns the Nth token in <string> when it is split by <delimiter>
  • TOKENCOUNT(<string>, <delimiter>) – Returns the total number of tokens in <string> as determined by <delimiter>

TOKEN(,,) will be particularly useful when manually parsing columns from a flat file. When used in conjunction with TOKENCOUNT it can be used to return the last token in a string like so:

  • TOKEN( [SomeTextColumn], “,”, TOKENCOUNT([SomeTextColumn], “,”) )

I guess they do sometimes read Connect Smile

Different style of Success/Failure Indicator

In SSIS2008R2:

image image

And now in Denali:

image image

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:

image

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:

image

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

image

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

image

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:

image

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

image

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”

image

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.

@jamiet

Other blog posts related to SSIS in Denali CTP3:

Published Tuesday, July 12, 2011 10:32 PM by jamiet

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

 

Peter Schott said:

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.

July 12, 2011 7:39 PM
 

Roger Noble said:

Hi Jamie, Great post, I can't wait to check them out myself. BTW Sort by name is also available in BIDS 2008.

July 12, 2011 11:32 PM
 

jamiet said:

Roger,

Its in BIDS 2008 if you have BIDS Helper installed, its not in the core offering.

Regards

JT

July 13, 2011 3:09 AM
 

Koen Verbeeck said:

Pretty amazing shizzle!

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)

July 13, 2011 5:04 AM
 

jamiet said:

Watch this space Koen ;)

July 13, 2011 5:41 AM
 

Julie Smith said:

Jamie,

Excellent summary.  Thank you!

July 14, 2011 2:12 PM
 

KB said:

August 10, 2011 3:20 PM
 

Kim Hutcheson said:

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 :-)

August 31, 2011 9:17 PM
 

Keerthi said:

Hi Jamie,

Great post...

Support for flat file with variable columns, new functions like Token and TokenCount and shared connection managers will make development easy.

It would be great if there will be an option to add header and footer to a destination flat file.

September 6, 2011 9:50 AM
 

SSIS Junkie said:

SQL Server codename Denali was presented to the world yesterday and with it a promise that there's a

September 7, 2011 10:43 AM
 

shaun said:

Nice summary.

It seems you can't assign var config values directly to task properties?

Like using configuration manager in 2008. I guess you pass in parameters and then use expressions and to pass parameter valaues to the desired properties.

October 6, 2011 6:17 AM
 

jamiet said:

Shaun,

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.

Ordinarily though, yes, expressions are the way forward.

JT

October 6, 2011 6:38 AM
 

Subhash Subramanyam said:

Enlightened. Looking forward for your DataTaps Post. :)

January 6, 2012 1:26 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement