Denali : Dependency Services

A blog of Stef Bauer shows an interesting new feature of Denali Dependency Services. I've played a bit with it and it's great! It's a great feature and it's surprising that i haven't read more about it on one of the public sites or popular blogs (except Stef). Okay, what is Dependency Services? Well, with Dependency Services it's possible to browse your SSIS package and execute an impact and lineage analysis. More information can be found on MSDN. Denali Dependency services supports at this moment the following objects:

• Database server
• Database
• Table
• View
• Column
• TSQL Stored Procedure
• File
• Package
• Connection Manager
• Task Component
• Source Component
• Destination Component
• Transformation Component
• External Input and Output Columns
• Variable
• XML Document

There are also three videos available:

• Extract Dependency Information Using the DependencyExtractor Command Prompt Tool
• Extract and View Dependency Information
• Extract and View Dependency Information for SQL Server Objects Using the MSSQL Provider

Installing Dependency Services
Dependency Services is not available in the SSMS explorer, strangly enough. You have to install Dependency Services before it's visible and usable in SSMS. The first thing you have to execute is this :

ialsetup.cmd -f "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" -s localhost"

Below you can see the results from execution this command in a DOS window:

Now an extra option is available in the SSMS explorer : Dependency Services.

The first step that i took was updating the providers

But an error occurred :

In the log file the following information is available:

AppDomain 4 (DependencyCatalog.dbo[runtime].3) created.

Unsafe assembly 'dependencybridge, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 4 (DependencyCatalog.dbo[runtime].3).

On MSDN read more information and i noticed that i've forgotten this piece of T-SQL:

USE DependencyCatalog
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
GO

Updating the providers succeeds now:

More information is now available in the SSMS explorer:

Choosing properties at the provider "SSIS" will show this screen:

Choosing properties at the provider "MSSQL" will show this screen:

Okay let's try to create an extraction point :

With the option "Launch Extraction Point Creation Wizard" the following wizard is started:

Choose 'Next':

Press 'OK' and 'Next':

Press 'Next':

Press 'Finish'. Now there are other options available: 'Validate', 'Update Catalogs' and 'Sync Now'. At this moment it's not clear to me what these options means. In one the videos the instructor walked through the steps and proceeded with the dependency designer without an explanation.

The first option (validate) :

 

The next option is "Update catalogs". Use the Extraction Point Catalogs dialog box to update the list of available catalogs that contain objects you can extract metadata for. You add and remove catalogs from the extraction point that you use to extract the metadata. A database is a type of catalog (MSDN).

And the last option 'Sync now':

Below i've included a couple of screenshots of the dependency designer:

I opened a couple of boxes:

Here is another screenshot:

Conclusion:
As is mentioned earlier this is a great feature analyzing your SSIS package. More improvement needs to be done. For instance i haven't found a zoom function and you realy need that as the last screenshot indicates.
This will help troubleshooting your SSIS packages a lot. In my one of my SSIS projects it's very difficult to find a problem when you're using all kind of features like variables, checkpointing, expressions, transactions, databaseconnections,  etc.

Greetz,

Hennie