Extract & Save SQL Statements in Execute SQL Tasks using C#

By | April 7, 2011

Abstract

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

Requirements

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 Tasks 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 that 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 hard disk:
Package p = app.LoadPackage(“C:\\TEMP\\your_pkg_name.dtsx”, null);

Establish a connection to 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
foreach (string s in 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.

4 thoughts on “Extract & Save SQL Statements in Execute SQL Tasks using C#

  1. panda coupon

    obviously like your web site but you need to check the spelling on several of your posts. A number of them are rife with spelling issues and I find it very troublesome to tell the truth nevertheless I will surely come back again.

    Reply

Leave a Reply