Tracking parameter mappings in SSIS

Question

In an SSIS project, I know how to set up a variable to hold the connection string for a Connection Manager - but how do I go the other way? ie - I have an existing SSIS package and want to find out the name of the variable which provides the connection string for a particular Connection Manager.

I can find loads of references to how to set it up, and did expect that going to Connection manager>Properties>Expressions would show me, but it doesn't. I did manage to find it by going to Package Configuration Organizer, picking sensibly named parameters, going to edit them, going to the second dialog of the wizard to find the Exported property name. This can't be the only way, surely?

Regards, Stewart

EDIT - This is in Visual Studio 2008

Answer 1

Thanks to the above answers, I realised that it wasn't a variable, but a configuration. Once that became clear, more Googling led to an explanation of why the Target Object is blank in the Package Configuration Organizer (there can be more than one).

The answer is in the config database; the PackagePath entry holds \Package.Connections[connection manager name].Properties[ConnectionString], so to find out where a particular mapping comes from, use something like this.

    SELECT TOP 1000 [ConfigurationFilter]
  ,[ConfiguredValue]
  ,[PackagePath]
  ,[ConfiguredValueType]

FROM [Database].[dbo].[Configurations_table] where ConfiguredValue like '%object in which you are interested%'

(not sure why all that didn't go in the code block).

I still think it should be easier, but I hope this helps others.

Regards, Stewart

Answer 2

Great answer by billinkc. In addition to that answer (or rather fleshing out the "inspect the object model and look at what's configured" part), I run a script at the start of any package that adds the values of all connection manager connection strings to the output window, followed by the connection string expression for each manager. In addition it loops through all the variables that have been specifed for use in the script and outputs the value. Not so useful in production but very useful when developing/testing.

Just add a script task to the start of the package flow, specify any variables you want to debug then add the following code to the script:

    'Report number of connections
    Dts.Events.FireInformation(99, "debug", "number of connections = " & Dts.Connections.Count, "", 0, True)

    'Loop through connection collection
    For Each cConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager In Dts.Connections

        'Report connection string value
        Try
            Dts.Events.FireInformation(99, "debug", "connection """ & cConnection.Name & """ value = " & cConnection.ConnectionString, "", 0, True)
        Catch
        End Try

        'Report connection string expression
        Try
            Dts.Events.FireInformation(99, "debug", "connection """ & cConnection.Name & """ constring  expression = " & cConnection.GetExpression("ConnectionString"), "", 0, True)
        Catch
        End Try

    Next

    'Report number of variables
    Dts.Events.FireInformation(99, "debug", "Number of Variables = " & Dts.Variables.Count, "", 0, True)

    'Loop through variables collection
    For Each vVariable As Microsoft.SqlServer.Dts.Runtime.Variable In Dts.Variables

        'Report variable value
        Try
            Dts.Events.FireInformation(99, "debug", "Variable """ & vVariable.Name & """ value = " & vVariable.Value, "", 0, True)
        Catch
        End Try

    Next

Answer 3

First of all, grab BIDSHelper it's a free add-in for visual studio and at a minimum it helps identify when elements have Expressions and Configurations applied to them. One will have a teal highlight, the other a fuschia colored and yes, an object can have both.

The first scenario you described, look at the Properties, Expressions and identify the use of expressions. Other objects, you might need to look at an Expressions tab.

Configurations work differently. You can use an Environment Variable, Registry Value, Parent Package Value, XML file or SQL Server table. The first three provide a 1:1 mapping between a configuration value and a configured item (variable, connection manager, etc). XML and SQL Server can configure many items. The order configurations are applied is important as you could have 5 configuration entries and each one of them modify the same setting with different values. There is also a difference between how 2005 and 2008 applies configurations so take a peek at Understanding How Integration Services Applies Configurations

When a package loads, BIDS will indicate what configurations it is attempting to load (look in your output window). Beyond BIDS highlighting and those messages, those messages are your other clue that configurations exist and are being applied. That's also your opportunity for detecting missing configurations (I expected to find configuration X and didn't find it = the configuration resource doesn't exist) or (I expected to configure property X but could not find it = the thing being configured does not exist)

I have found the best approach is to define a common set of configurations (Sales connection, warehouse connection) that all the applications in an environment use and use a consistent configuration naming approach. We then use custom configurations for project level things (the path for input and output for the InsuranceProcessing packages is would apply across all the packages but would be different for Sales) and then a third set of configurations that is package specific. We use SQL Server tables for this as it makes inspecting values much easier than gloming through lots of ugly XML.

Lots of information, but nothing that directly answers your question. Sorry about that. You might be able to inspect the object model and look at what's configured but that's ugly.