Tuesday 21 October 2014

Querying SSIS Parameter information from SSISDB


On your SSIS SQL Server you can run the following code against the SSISDB database to investigate parameter information stored in the SSIS catalog

There are multiple versions or the parameter stored in the interal.object_parameters table so this will get only the latest version of the each parameter/connection

The Query


/*
    Check package parameters and connection string settings
    for a project
*/
WITH projectVersions AS
(
    SELECT p2.name,p2.project_id, MAX(p1.project_version_lsn) versionlsn
    FROM internal.object_parameters p1
    INNER JOIN internal.projects p2 ON  p1.project_id = p2.project_id
    GROUP BY p2.name,p2.project_id
)
SELECT  CASE LEFT(params.parameter_name,3) 
        WHEN 'CM.' THEN
            'Connection Manager'
        ELSE
            'Package Parameter'     
        END as Area ,  
        params.object_name ,
        params.parameter_name ,
        params.parameter_data_type ,
        params.design_default_value ,
        params.default_value
FROM    SSISDB.internal.object_parameters params
        INNER JOIN projectVersions proj ON params.project_id = proj.project_id
WHERE   proj.versionlsn = params.project_version_lsn
--AND proj.name = 'EDI' -- change for another ssis project
ORDER BY 1


Example of the output

Areaobject_nameparameter_nameparameter_data_typedesign_default_valuedefault_value
 Package ParameterEDICommandStringStringC:\SSIS\C:\SSIS\EDI
 Package ParameterEDIServerNameStringStockSourceNULL
Connection ManagerEDICM.EDI.ConnectionStringStringData Source=x;Initial Catalog=x;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;NULL

No comments:

Post a Comment