Extracting SQL statements from a SSIS/DTSX package

Question

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;
}

Answer 1

There is another way.

You can create a custom log event. It is written about here:

enabling custom logging for ssis tasks

Then you just need to run the package and parse the log file that is created.

I'm not sure about DTS though but that should get all the SQL from expressions etc. in an SSIS package.

Answer 2

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)') <>''