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
| Area | object_name | parameter_name | parameter_data_type | design_default_value | default_value |
|---|---|---|---|---|---|
| Package Parameter | EDI | CommandString | String | C:\SSIS\ | C:\SSIS\EDI |
| Package Parameter | EDI | ServerName | String | StockSource | NULL |
| Connection Manager | EDI | CM.EDI.ConnectionString | String | Data Source=x;Initial Catalog=x;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False; | NULL |
