Abstract
This article explains how to extract and save SQL Statements in Execute SQL Tasks of an SSIS package using C# programming language.

Requirements

• Microsoft Visual Studio 2008
• SQL Server 2008

Article
I have been recently been tasked by my boss at work to extract the SQL Statements in an SSIS package that contained over 1400 Execute SQL Tasks steps. Consequently, I programmed a script task using C# that reads each SQL Task and save the SQL statement to a SQL Server 2008 table. In this article, I have created a similar script using the example I discussed in the article which can be found here
Okay, let’s begin.
Launch Visual Studio 2008 and create an Integration Services Project. After the default (new) package has launched, drag a script task to the control flow pane.
Right-click to edit the script task. In your Script Task Editor ensure that you have selected Microsoft Visual C# as your programming language.
At the bottom of your Script Task Editor, click “Edit Script”.
Add the following references:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
Proceed to load the package (that contains the Execute SQL Tasks you would like to extract and save).
Application app = new Application();

Load package from SQL Server:
Package importPackage = app.LoadFromSqlServer(@”\\your_pkg_name”, “(local)”, null, null, null);

or load it from the Integrations Services Server:
Package importPackage = app.LoadFromDtsServer(@”File System\your_pkg_name”, “yourserver”, null);

or load it from a hard disk:
Package p = app.LoadPackage(“C:\\TEMP\\your_pkg_name.dtsx”, null);
Establish a connection to the SQL database where you will store the extracted SQL statements:
SqlConnection connectiont = new SqlConnection(
string.Format(“Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “(local)”, “your_db”));
SqlCommand commandt;
connectiont.Open();

string src_query2 = “”;
string src_query3 = “”;
string src_query = “”;
string sql_task_name = “”;

Loop through Execute SQL Tasks in the package:
foreach (Executable executable in importPackage.Executables)
{
DtsContainer Seq_container = (DtsContainer)executable;
if (executable.GetType().Name == “TaskHost”)
{
TaskHost loop = (TaskHost)executable;
ExecuteSQLTask sqlTask = (ExecuteSQLTask)loop.InnerObject;
src_query2 = sqlTask.SqlStatementSource;
src_query3 = src_query2.ToUpper();
src_query = src_query3;
sql_task_name = Seq_container.Name;
//split SQL query on keyword “GO”
string source = src_query;
string[] stringSeparators = new string[] { “GO” };
string[] result;

result = source.Split(stringSeparators, StringSplitOptions.None);
//for each extracted and split statement, insert it into a table
for each (strings in the result)
{
commandt = new SqlCommand(“INSERT INTO Your_tb VALUES”(@SRC_Q,@SQL_NAME)”,
connectiont);
commandt.Parameters.Add(new SqlParameter(“@SRC_Q”,s));
commandt.ExecuteNonQuery();
}
}
}
Dts.TaskResult = (int)ScriptResults.Success;
Conclusion
It’s that simple!
You can now execute your script task and the Execute SQL Tasks statements will be extracted and saved into your table.