Extract SQL Statements in Execute SQL Tasks and save them into an Excel Workbook using C#

By | May 10, 2011

Abstract

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

Requirements

Article

This article continues from another article which can be found here.

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”.

The next step is to insert references.

In the Project Explorer, right click “References” and click “Add Reference”. Click the “COM” tab and Choose Microsoft Excel 14.0 Object Library.

Add the following namespaces:
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

The rest of the references should be declared as follows:

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

Declare variables that will be used to store extracted queries:

string src_query = “”;
string sql_task_name = “”;
Int32 i = 1;

Create an instance of an excel application as follows:

Excel.Application sS_excelApp;
Excel.Workbook sS_excelWorkBook;
Excel.Worksheet sS_excelWorkSheet;
object misValue = System.Reflection.Missing.Value;

sS_excelApp = new Excel.ApplicationClass();
sS_excelWorkBook = sS_excelApp.Workbooks.Add(misValue);

sS_excelWorkSheet = (Excel.Worksheet)sS_excelWorkBook.Worksheets.get_Item(1);

// Create Headings
sS_excelWorkSheet.Cells[1, 1] = “src_query”;
sS_excelWorkSheet.Cells[1, 2] = “sql_task_name”;

The rest of the code is as follows:
foreach (Executable executable in importPackage.Executables)
{
DtsContainer container = (DtsContainer)executable;
if (executable.GetType().Name == “TaskHost”)
{
i = i + 1;
TaskHost loop = (TaskHost)executable;
ExecuteSQLTask sqlTask = (ExecuteSQLTask)loop.InnerObject;
src_query = sqlTask.SqlStatementSource;
sql_task_name = container.Name;

string source = src_query;
string[] stringSeparators = new string[] { “GO” };
string[] result;

result = source.Split(stringSeparators, StringSplitOptions.None);

foreach (string s in result)
{
// Insert into new records into the workbook
sS_excelWorkSheet.Cells[i, 1] = s;
sS_excelWorkSheet.Cells[i, 2] = sql_task_name;
}
}
}

// Save workbook
sS_excelWorkBook.SaveAs(“C:\\TEMP\\selectSIFISO_Book.xls”, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// Close workbook
sS_excelWorkBook.Close(true, misValue, misValue);
sS_excelApp.Quit();

Dts.TaskResult = (int)ScriptResults.Success;

We then save the package in a file system.

SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_Execute_Sql_Tasks.dtsx”, dyna_pkg, null);

Conclusion

It’s that simple!

You can now execute your script task and the package will be created in location you specified.

58 thoughts on “Extract SQL Statements in Execute SQL Tasks and save them into an Excel Workbook using C#

  1. facebook poker chips

    When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several emails with the same comment. Is there any way you can remove me from that service? Many thanks!

    Reply
  2. Denise Wagman

    Heya…outstanding blog. Grateful I recently found it thru Google search, to bad it turned out on page 8 of search results. It appears as though your operating WordPress in your blog, you could look into this plugin for WordPress at http://www.myseopressor.com it’s tremendously improved me with my online site rankings in Google. I would hate to see your useful piece of content not be discovered by other folks. Good fortune with your site.

    Reply
  3. keratin straightener

    Usually I don’t comment that often but I wanted to let you knwo that there is something wrong with your comment field thingy. It keeps giving me a black page when I click on ‘Comment’ :s

    Reply
  4. scrapebox

    Hey, usually I don’t comment that often but it looks like you are still using the old WordPress comment system. Personally I use Disqus (or however it is spelled). It made my visitors a lot more involved. Just FYI 🙂 Regards, Celina

    Reply
  5. Lorry Insurance

    I dont know what to say. This blog is wonderful. Thats not really a actually huge statement, but its all I could come up with after reading this. You know so significantly about this subject. So a lot to ensure that you made me want to understand far more about it. Your blog is my stepping stone, my friend. Thanks for the heads up on this subject.

    Reply
  6. clipless pedals

    What service do you use for RSS feed? Is it feedburner? I ask because I cant figure out how to install a RSS service on my Blogger blog. I wouldn’t get any of that techy stuff if my life would depent on it :s So if you use a RSS feed service please let me know. Thanks! Jen

    Reply
  7. gun safe reviews

    Ok really, usually I don’t comment on your posts but I really need to ask something. I noticed that when I try to post a comment the page after I click the button is blank. Is that because of some spamfilter? If so what is it? Also where can I download it, I would like to try it on my own blog! Would be much appreciated. Regards, Jenni

    Reply
  8. Mexican Train Dominoes

    Why didnt I think about this? I hear specifically what youre saying and Im so happy that I came across your blog. You actually know what youre talking about, and you produced me feel like I really should learn more about this. Thanks for this; Im officially a large fan of your weblog.

    Reply
  9. cheap backpacks

    Fantastic weblog! I dont believe Ive seen all of the angles of this subject the way youve pointed them out. Youre a true star, a rock star man. Youve got so much to say and know so significantly about the subject that I feel you ought to just teach a class about it…HaHa!

    Reply
  10. Gap Year Travel Insurance

    I dont know what to say. This blog is amazing. Thats not truly a actually massive statement, but its all I could come up with right after reading this. You know so much about this subject. So significantly to ensure that you made me desire to discover much more about it. Your blog is my stepping stone, my friend. Thanks for the heads up on this subject.

    Reply
  11. toddler backpacks

    Youre so proper. Im there with you. Your weblog is absolutely worth a read if everyone comes across it. Im lucky I did simply because now Ive got a whole new view of this. I didnt realise that this problem was so essential and so universal. You definitely put it in perspective for me.

    Reply
  12. Photo Frames

    What I dont comprehend is how youre not even a lot more well-known than you are now. Youre just so intelligent. You know so considerably about this subject, created me think about it from so a lot of different angles. Its like folks arent interested unless it has something to do with Lady Gaga! Your stuffs excellent. Keep it up!

    Reply
  13. Car Insurance Groups

    The beauty of these blogging engines and CMS platforms will be the lack of limitations and ease of manipulation that enables developers to implement wealthy content and ‘skin’ the web site in such a way that with extremely small effort one would in no way notice what it really is creating the internet site tick all with out limiting content and effectiveness.

    Reply
  14. Insulated Lunch Bags

    Howdy, i read your weblog occasionally and i own a comparable one and i was just wondering should you get a lot of spam comments? If so how do you prevent it, any plugin or anything you are able to advise? I get so a lot lately it is driving me mad so any assistance is very a lot appreciated.

    Reply
  15. fluval aquariums

    I just cant quit reading this. Its so cool, so full of information that I just didnt know. Im glad to see that folks are actually writing about this problem in such a intelligent way, showing us all different sides to it. Youre a great blogger. Please keep it up. I cant wait to read whats next.

    Reply
    1. Sifiso

      Your feedback gives people like us the courage and inspiration to keep blogging. I appreciate it, many thanks

      Reply

Leave a Reply