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

 Computer, Sharepoint, Software  Comments Off on SSIS package to Send Email based on SP Task List Due Dates
Feb 262014
 
Share

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:

 Posted by at 12:14 am  Tagged with:

SSIS transformation for Sharepoint List Adapter

 Sharepoint  Comments Off on SSIS transformation for Sharepoint List Adapter
Jul 242013
 
Share

I was using the SSIS Sharepoint List Adapter and ran into an issue with importing calculated columns and People columns.

The import process was inserting USERID;#Username into my People column in the destination database table. It was

also inserting datetime;#12/12/2013 into my date fields.

SqlTask2

 

I found a handy script component that will transform those columns on the fly.

You have to insert the script component as a transformation between the Sharepoint List Source and your destination. You must also allow
all columns to pass through. The column you want to manipulate must be set to Read/Write.

SqlTask

 

 

Paste this in the Input0_ProcessInputRow function…

string[] delimiter = new string[] {“;#”};
string[] peopleSplit = Row.PeopleField.Split(delimiter,StringSplitOptions.None);
string result = string.Empty;

for (int i = 1; i < peopleSplit.Length; i += 2)
{
result += peopleSplit[i];
if (i != peopleSplit.Length – 1)
{
result += “, “;
}
}

Row.PeopleField = result;

Change the “PeopleField” string to your appropriate column.