- Database server
- TSQL Stored Procedure
- Connection Manager
- Task Component
- Source Component
- Destination Component
- Transformation Component
- External Input and Output Columns
- 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
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=184.108.40.206, 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:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
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 :
Press 'OK' and '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:
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.