T-SQL: Retrieve Connectionstring Details from a SSIS Package
Recently there was a question in one of the MSDN forums asking whether we can extract connection string details used inside a SSIS package. The solution suggested required the use of a SSIS package which loads the required SSIS package code and used script task inside to parse and get the details. This article deals with an alternate way of getting the same using Transact SQL code.
The SSIS package code behind is basically a XML document with a standard structure. This can be loaded into a table and Xpath functions in Transact SQL can be utilized for parsing the XML document and getting the required details out of it.
The XML can be loaded using OPENROWSET..BULK syntax explained here
Once this is done, the XML can be parsed using the Xpath functions explained here
The Transact SQL stored procedure code for extracting the details would be as follows
CREATE PROC GetConnectionstringDetailsfromSSIS
@PackagePath varchar(200)
AS
DECLARE @PackageCode table
(
PackageXML xml
)
DECLARE @Params nvarchar(100) = N'@PackagePath varchar(200)',@SQL nvarchar(4000)=N'SELECT *
FROM OPENROWSET(BULK ''' + @PackagePath + N''',SINGLE_BLOB) AS a'
INSERT @PackageCode
EXEC sp_executesql @SQL,@Params,@PackagePath =@PackagePath
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT t.ConnStr AS ConnectionString,t.ConnID AS ConnectionManagerID,
b.value('@name','varchar(100)') AS ConnectionType
FROM
(
SELECT PackageXML,q.value('.','varchar(1000)') AS ConnStr,
n.value('DTS:Property[@DTS:Name="DTSID"][1]','varchar(50)') AS ConnID
FROM @PackageCode t
CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager')m(n)
CROSS APPLY n.nodes('DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]')p(q)
WHERE p.q.value('.','varchar(100)') > ''
)t
OUTER APPLY PackageXML.nodes('//connections/connection[@connectionManagerID=sql:column("ConnID")]')a(b)
GO
Once the procedure is created you can invoke it as below
EXEC GetConnectionstringDetailsfromSSIS <SSIS package full path>
This will execute the procedure and will return you the connectionstring details from the SSIS package under consideration in the below format
ConnectionString ConnectionManagerID ConnectionType
--------------------------------------------------------------------------------------------------------------------------------
<fullConnectionstring> {80EBFB1B-5188-4B26-B698-26C6868BE713} OleDbConnection
.....
This approach comes handy in cases where we need to retrieve similar details from existing SSIS packages. By modifying the Xpath expression used above we would be able to retrieve other details as well from the package.