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.

SSISTasksDueList


Steps:
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 codeplex.com

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:

SSISTasksDueSQLTask2


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
        };
        #endregion

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

            Dts.VariableDispenser.LockForWrite("User::EmailBody");
            Dts.VariableDispenser.LockForWrite("User::ItemTitle");
            Dts.VariableDispenser.LockForWrite("User::ItemDue");
            Dts.VariableDispenser.LockForWrite("User::ItemEmail");
            Dts.VariableDispenser.LockForWrite("User::DaysOut");
            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
Bitnami