Extracting SQL statements from a SSIS/DTSX package

sql-server, ssis, visual-studio

Solution

The following Query is helpful to retrieve all the sql statements inside the SSIS package

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS  
DTS,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask) 

-- Query to Extract SQL Tasks with Name and SQL Statement 
SELECT Pkg.props.value('../../DTS:Property[@DTS:Name="ObjectName"]
[1]','varchar(MAX)') ObjectName, 
Pkg.props.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') AS 
SqlStatement FROM (select cast(pkgblob.BulkColumn as XML) pkgXML from 
openrowset(bulk 'Your DTS package with name and location Path',single_blob) 
as pkgblob) t CROSS APPLY pkgXML.nodes('//DTS:ObjectData//SQLTask:SqlTaskData') Pkg(props) 
UNION 

-- Query to Extract DTS Pipline task with Name and SqlCommand 

SELECT Pkg.props.value('../../../../DTS:Property[@DTS:Name="ObjectName"]
[1]','varchar(MAX)') ObjectName, 
Pkg.props.value('data(./properties/property[@name=''SqlCommand''])[1]', 
'varchar(max)') SqlStatement FROM(select cast(pkgblob.BulkColumn as XML) 
 pkgXML from openrowset(bulk 'Your DTS package with name and location 
 Path',single_blob) as pkgblob) t CROSS APPLY 
pkgXML.nodes('//DTS:Executable//pipeline//components//component') Pkg(props) 
WHERE Pkg.props.value('data(./properties/property[@name=''SqlCommand''])
[1]', 'varchar(max)') <>''

Problem

I am looking for something to extract all the SQL queries present in my SSIS/DTSX package. But nothing is helping me till now. I already had a look at Microsoft.SqlServer.DTS API's from Microsoft. But they are extracting some queries straight forward. But the queries that are present in DTS:variable TAG, they are not extracted. I want something in .Net framework. As i need to use the output to perform some other task as well. I am using C#. Sample code as follows. Does not address all the situations ``` // this function takes the list of task hosts as input // and gives all the queries present in taskhosts. public static string ExtractQueriesFromTasks(List<TaskHost> Tasks) { string src_query = ""; foreach (TaskHost executable in Tasks) { DtsContainer Seq_container = (DtsContainer)executable; if (executable.InnerObject.GetType().Name == "ExecuteSQLTask") { ExecuteSQLTask sqlTask = (ExecuteSQLTask)executable.InnerObject; string src_query2 = sqlTask.SqlStatementSource; src_query = src_query + "\n" + src_query2.ToUpper(); } if (executable.InnerObject.GetType().Name == "__ComObject") { IDTSPipeline100 sqlTask = (IDTSPipeline100)executable.InnerObject; Console.WriteLine(Microsoft.VisualBasic.Information.TypeName(executable.InnerObject)); //ExecuteSQLTask sqlTask = (ExecuteSQLTask)executable.InnerObject; //string src_query2 = sqlTask.SqlStatementSource; //src_query = src_query + "\n" + src_query2.ToUpper(); } if (executable.InnerObject.GetType().Name == "ScriptTask") { ExecuteSQLTask sqlTask = (ExecuteSQLTask)executable.InnerObject; string src_query2 = sqlTask.SqlStatementSource; src_query = src_query + "\n" + src_query2.ToUpper(); } } return src_query; } ```

Original source