Had a request to send notification emails against a SharePoint Task List when the Due Dates were 90, 60, 30 and 5 days out.
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:
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:
