various shenanigans.

SSIS package to Send Email based on SP Task List Due Dates

Had a request to send notification emails against a SharePoint Task List when the Due Dates were 90, 60, 30 and 5 days out.


0. You will need to create the 6 variables show on the left.

1. I am dropping and importing the SP List Data each time the package is run with
a SQL Task.

2. I am using the SharePoint adapter add-ins from

3. The query to pull the data into SSIS is screenshot #1 below. Make sure your
ResultSet is set to “Full Result Set”

3a. Click on Result Set and set it to your global QueryResults variable.
Screenshot #2.

4. Add a Foreach Loop Container. Use the Foreach ADO Enumerator and set the
ADO Object to your global QueryResults variable. Screenshot #3

4a. Set your variable mappings to your Item global variables. Screenshot #4.

5. Add a Script Task with the code snippet below.

6. Add a Send Email Task. Create your SMTP connection.
Add a From email address.
Set MessageSourceType to Variable.
Set MessageSource to your global EmailBody variable.
Screenshot #5.

7. Modify your Send Email Task Expressions setting the ToLine to your global
EmailBody variable. Important Note: Set the Delay Validation
property to True.

8. Deploy and schedule your package.

Screenshot #1

Screenshot #2:


Screenshot #3:

Screenshot #4:

Script Task Code:

/*Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_7f59d09774914001b60a99a90809d5c5.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

        public void Main()
            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;

            Dts.VariableDispenser.GetVariables(ref varCollection);

            //Set the header message for the query result
            if (varCollection["User::EmailBody"].Value == string.Empty)
                header = "You have been assigned a task:nn";

                varCollection["User::EmailBody"].Value = header;
            //Clear previous message is applicable

           //Create Body 
            message = "The due date for this task is "+varCollection["User::ItemDue"].Value+"nnThis task is "+varCollection["User::DaysOut"].Value+" days out from the due date. nn";

            varCollection["User::EmailBody"].Value = varCollection["User::EmailBody"].Value + message;

            Dts.TaskResult = (int)ScriptResults.Success;

Screenshot #5:

Screenshot #6:

Close Bitnami banner