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;
}
DTSX files are just XML files. Just rename to xml, and open with an editor.
After you have created a SSIS package using the SQL Server option, you can view it in the SQL Server Management Studio window: expanding the Data Transformation Services folder, and then double-click Local Packages. All of the SSIS packages on the selected server are then displayed in the pane on the right.
To export to the SSIS package store, select the SSIS Package Store option, and specify the server. Click the browse button (...), expand the SSIS Packages folder, and select the folder to which you want to save the package. Optionally, enter a new name for the package in the Package Name text box. Select OK.
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)') <>''
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With