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