从 SSIS/DTSX 包中提取 SQL 语句 [英] Extracting SQL statements from a SSIS/DTSX package
问题描述
我正在寻找一些东西来提取我的 SSIS/DTSX 包中存在的所有 SQL 查询.但到目前为止,没有什么能帮到我.
I am looking for something to extract all the SQL queries present in my SSIS/DTSX package. But nothing is helping me till now.
我已经看过来自 Microsoft 的 Microsoft.SqlServer.DTS API.但是他们正在直接提取一些查询.但是存在于 DTS:variable TAG 中的查询不会被提取.
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.
我想要 .Net 框架中的东西.因为我还需要使用输出来执行其他一些任务.我正在使用 C#.
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;
}
推荐答案
以下查询有助于检索 SSIS 包内的所有 sql 语句
;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)') <>''
这篇关于从 SSIS/DTSX 包中提取 SQL 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!