[SSIS] Finding your connection strings in SQL Server 2012

(update 2013/07/18: hey, grabbing the data source doesn't actually get the default value.  That's stored in the same tables, but I need to figure out how to pull those out (ObjectType, maybe).  Give me a few days.)

(update 2013/07/22 here's what I've cobbled together, though it needs to be integrated with the below

SELECT  projects.name, ConnStr.object_name, ConnStr.parameter_name, ConnStr.description,
        ConnStr.design_default_value, ConnStr.default_value,
        SrvName.parameter_name, SrvName.description, SrvName.design_default_value,
        SrvName.default_value,
        --the catalog name
        DBName.parameter_name, DBName.design_default_value, DBName.default_value
    FROM ssisdb.catalog.projects
    INNER JOIN     [SSISDB].[catalog].[object_parameters] ConnStr
    ON SSISDB.catalog.projects.project_id = ConnStr.project_id
--get the server name.  Left outer join as it doesn't appear in the table otherwise
    LEFT OUTER JOIN [SSISDB].[catalog].[object_parameters] SrvName
    ON connstr.[object_name] = SrvName.[object_name]
    AND connstr.parameter_name LIKE 'CM%ConnectionString'
    AND srvname.parameter_name LIKE 'CM%ServerName'
--get the database name as well
    LEFT OUTER JOIN [SSISDB].[catalog].[object_parameters] DBName
    ON connstr.[object_name] = DBName.[object_name]
    AND connstr.parameter_name LIKE 'CM%ConnectionString'
    AND DBName.parameter_name LIKE 'CM%InitialCatalog'
WHERE ConnStr.parameter_name LIKE 'CM%ConnectionString'
    AND LEFT(connstr.parameter_name, LEN(connstr.parameter_name) - CHARINDEX('.',REVERSE(connstr.parameter_name)))
     = LEFT(SrvName.parameter_name, LEN(SrvName.parameter_name) - CHARINDEX('.',REVERSE(SrvName.parameter_name)))
    AND LEFT(connstr.parameter_name, LEN(connstr.parameter_name) - CHARINDEX('.',REVERSE(connstr.parameter_name)))
     = LEFT(DBName.parameter_name, LEN(DBName.parameter_name) - CHARINDEX('.',REVERSE(DBName.parameter_name)))
ORDER BY projects.name, ConnStr.object_name, connstr.parameter_name
)


Part of the "fun" with SSIS has always been the myriad of ways a parameter can be set.  Variables, Configuration Files, Properties, Jobs.... and probably 1 or 2 others, just to confuse things.

I've already posted (http://thebakingdba.blogspot.com/2013/05/ssis-finding-your-connection-strings-in.html) about the connection strings in 2008/R2.  This is the version for 2012... 

In theory, it should be simple - if you're using the new methodology, you're developing with SSISDB, and using it to hold your configuration values.   Thus, it has all the connection string details within.

However, it can be overridden at the job level... so you potentially need both.  What I do is grab the most recent run of the package, as well as what SSISDB says it has for the package, and present both.

Let me know if there are any questions.    I see on mine, for instance, dupes where the connection string is the same, but the parameter name is different.  Not sure why yet.

Let me know if you see any problems or places to improve, or heck, maybe I'm just doing it wrong.