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.
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:
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:
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:
In Denali the 4000 character limit has gone away.
New expression language functions
We have three new functions in the expression language:
- 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 
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.
@jamiet
Other blog posts related to SSIS in Denali CTP3: