All geek questions in one place

Extract SQL statements from SSIS / DTSX

I am looking for something to extract all the SQL queries present in my SSIS / DTSX package. But still nothing helps me.

I already reviewed the Microsoft.SqlServer.DTS API from Microsoft. But they take some questions straight ahead. But the queries that are present in the DTS: variable TAG are not retrieved.

I want something in a .Net environment. Since I need to use output to perform another task. I am using C #.

Sample code as follows. Does not take into account all 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; }
+6
source share
2 answers

The following query is useful for retrieving all sql statements inside an 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)') <>''
+3
source

There is another way.

You can create your own log event. It is written here:

allows you to configure logging for ssis tasks

Then you just need to run the package and analyze the generated log file.

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

0
source

Source: https://habr.com/ru/post/944204/

More articles:


All Articles