SSIS: script task to write to file and send as email attachment

Problem:

Create a package which read a query and save the result in file and send that file as an attachment.

Solution:

The problem says that we do a select query on some database, save the result to a file and send them as attachment. Or you asked to write to file and leave it there. This file can be picked by any other process. In this case, I am going to send the file as an attachment through email.

Designing the package:

This package will consists of three task:
1. Execute SQL Task
2.  Script Task
3. Send Mail Task

Variables:

Lets create all the variables needed for this package.

1. varResultSet: object type. This variable will hold the result of our execute SQL task.
2. varNewFileDelimiter: This variable is used as file delimiter which will be used in script task.
3. varUploadDirectoryPath: This variable is used to save my file created and hold values where I will be saving my file.
4.varMyResultFileName: This variable hold file name. Set Expression value to true and under expression add following.
@[User::varUploadDirectoryPath]+ "\\"+"MyResult"  + "_" + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + Right("0" + (DT_WSTR,2)DAY(GETDATE()),2)  + (DT_WSTR,4)YEAR(GETDATE()) + ".csv"

Here I am saving my file as csv file with date added to file name.

5. varExceptionCount: This variable hold any exception counts.

Most of these variable will be used in script task and send mail task.

So lets go and add Execute SQL task to our package.

1. Execute SQL Task

Added connection manager which will be used. Right down your query in your sql statement and set ResultSet to Full Result Set.

Under Result set add the variable
Result Name = 0
Variable =varResultSet

This is all we have to do in the first task. Now if you execute this task, the result of your query will be held in this variable.

Let's add script task to consume this result and write to file.


2.  Script Task

In our script task, let's chose C# language and Under Read Only add following variables.

User::varResultSet,User::varMyResultFileName,User::varNewFileDelimiter

And In ReadWriteVariable, add User::varExceptionCount

Open your script task and add following code. Make sure that if you are using different variable name, change them at your end.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;  
using System.IO;
using System.Reflection;

namespace ST_7cdb67cf97304adab75a69c0b1d4688e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion



        public void Main()
        {
            try
            {
                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                A.Fill(dt, Dts.Variables["User::varResultSet"].Value);
                string filepath = Dts.Variables["User::varMyResultFileName"].Value.ToString();
               
                int i = 0;

                Dts.Variables["User::varExceptionCount"].Value = dt.Rows.Count;

                if (dt.Rows.Count > 0)

                {
                    StreamWriter sw = null;

                    sw = new StreamWriter(filepath, false);

                    for (i = 0; i <dt.Columns.Count; i++)
                        {
                   
                            if (i == dt.Columns.Count -1)
                        {
                            sw.Write(dt.Columns[i].ToString());
                        }
                    else
                        {
                            sw.Write(dt.Columns[i].ToString()+Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                        }
                    }
               sw.WriteLine();

                    foreach (DataRow row in dt.Rows)
                    {
                        object[] array = row.ItemArray;

                        for (i = 0; i < array.Length; i++)
                        {
                            if (i == array.Length - 1)
                            {
                                sw.Write(array[i].ToString());
                            }
                            else
                            {
                                sw.Write(array[i].ToString() + Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                            }
                        }
                        sw.WriteLine();
                    }
                    sw.Close();

            }
                    }
                        catch (Exception ex)
                        {
                         }
                 }
    }
}
       

Save this. Now if you run your package, you find your file in the folder.

3. Send Mail Task

Add a send mail task, add your connection manage, add email id where you want to send. In expression, select FileAttachment and set it your variable (in this case @[User::vvarMyResultFileName]

You are all set to send this to as attachment.