Category Archives: SSIS

How to send email from a SharePoint Calendar

SharePoint Calendar 2010, Expressions and SSIS

By Douglas Weinberg

Recently I had a specific business requirement using the SharePoint calendar. This calendar contained a list of birth dates and anniversary dates of people working at the company. I started by creating a list of yearly reoccruring events of birthdays and anniversaries all within the months of 2012. That was the easy part.

The tricky part was the second requirement to have the system automatically send out an email on the first of every month that listed those celebrants for that month. That was going to be more challenging. Below is a brief explanation of how I accomplished this proejct:

1) Start by creating a new calendar in SharePoint. I called mine BDAY/ANNIVERSARY.
2) Create a new view format choosing Standard View, with Expanded Recurring Events
3) Create 2 new columns: Message and MySpecialDate

    a. MySpecialDate – set it as DateTime column, required field
    b. Message – a caluculated column that you want to display in the calendar

I needed to use an expression formula that concatenates the person’s name with the type of celebration (birthday or anniversary). So I came up with the following:

=IF(Category="Birthday",CONCATENATE("Happy Birthday to ",Title),CONCATENATE("Happy Anniversary to ",Title))

4) Next I needed to add some content to the calendar so I started by adding list items one by one using the SharePoint customized form as shown below:

5) SharePoint calendar already have Birthday and Anniversary options for the Category event. You can, of course add more categories but that would be another post.
6) In order to display the message column in the calendar, you need to modify its view. On the SharePoint ribbon, go toe the CalenderTools tab and click Calendar > Modify View.

  1. a. Under the Time Interval section, choose the Begin and End time with MySpecialDate
  2. b.Under Calendar columns, choose Message for the Month View Title. I used Message for all the select options in this section. Your calendar should like similar to the screen grab:

EXPRESSIONS IN SSIS

7) Once you have your calendar populated with Birthday and Anniversaries, the next part is collect the items in the calendar using SSIS and CAML queries. This is where the magic happens. Before creating a new SSIS solution, you need to first make sure you have the SharePoint List Source adapter installed. This can be downloaded from the CodePlex website at:

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

Once you have properly installed the adapters in Visual Studio you are ready to begin.
8) From your SQL SharePoint server, create a new Integration Services Project from the Business Intelligence Projects template.
9) Start by adding a DataFlow task under the Control Flow tab.
10) Under the Connection Manager, right click to ann a new connection. Choose SPCRED connection type and use the default connection credentials.
11) Under the DataFlow tab, add a SharePoint List Source task.

  1. a. Under Properties, enter values for SiteUrl, SiteListViewName and SiteListName. These values should all point the new created calendar.

12) Next I added a Derived Column task to remove unwanted characters in my Message column and to remove the time value from the MySpecialDate column. In order to properly succeed, you will need to understand how to write expressions without syntax errors. For me, this was either ‘trial and error’ or Googling ‘expressions’. One useful site for dealing with datetime expressions was:

http://www.sqlis.com/post/Expression-Date-Functions.aspx

13) In the Derived Column task, I have 2 set of expressions.
a. MySpecialDate: change it to a string:

(DT_WSTR,30)(DT_DATE)MySpecialDate

b. Message: change to string and get the month and day value of MySpecialDate and get the current year:

REPLACE(Message,"string;#","") + " on " + (DT_WSTR,30)DATEPART("month",MySpecialDate) + "/" + (DT_WSTR,30)DATEPART("dd",MySpecialDate) + "/" + (DT_WSTR,30)DATEPART("yy",GETDATE())

14) The following task I needed was to put my data into a recordset so I added a RecordSet Destination task. However, I first needed to create an SSIS variable with a data type of object. To create variables, go to SSIS > Tools > Variables. I created a new one called sp_people, gave it a scope of Package and a data type Object.
15) I needed to pass my calendar data to the sp_people object. Open the editor for the Recordset Destination and under Custom Properties, add the variable, sp_people.
16) Your Data Flow tab should look like the following:

WORKING WITH CAML

So far you used the SharePoint List task to connect to your SharePoint calendar, collected your data from the Message and MySpecialDate columns and put them the a recordset object called sp_people. But how do you actually collect the data from the list? That is where CAML (Collaborative Application Markup Language) comes into play. Learning CAML is not too difficult once you familiar yourself with the syntax. Think of CAML as SQL and XML wrapped up in one.
One approach I had when I started writing my CAML code was first by calling the calendar using the SharePoint Object Model. Created a new Sandboxed solution webpart with the following CAML code:

string queryString = @"
                      <Where>
                      <DateRangesOverlap>
                      <FieldRef Name="EventDate"/>
                     <FieldRef Name="EndDate"/>
                      <FieldRef Name="RecurrenceID"/>
                      <Value IncludeTimeValue="TRUE" Type="DateTime">
                           <Month />
                               </Value>
                       </DateRangesOverlap>
                            </Where>
                       <OrderBy>
                         <FieldRef Name="EventDate" Ascending="TRUE"/>
                       </OrderBy>";

However, a more simplistic approach was using a 3rd party tool called CAML Query Builder. It’s the simplest way to get started. Downloaded U2U CAML Builder at http://www.u2u.net and connected to my SharePoint subsite where calendar is located:

All lists for the subsite will appear on the left column. Selected my list, clicked New Query and began buidling a query using the drop down list starting with the month of January. The Builder then displays the CAML code:

  <Query>
  <Where>
      <And>
        <Geq>
             <FieldRef Name="MySpecialDate" />
                    <Value IncludeTimeValue="TRUE" Type="DateTime">2012-01-01T11:03:17Z</Value>
         </Geq>
        <Leq>
              <FieldRef Name="MySpecialDate" />
                   <Value IncludeTimeValue="TRUE" Type="DateTime">2012-01-31T11:04:14Z</Value>
       </Leq>
    </And>
  </Where>
</Query>

I was getting closer to a solution but not quite there yet. I needed to figure out a way to have the solution automatically figure out the the first and last day of the current month. That’s where expressions come handy. Back in the Business Intelligence Developments studio, in order to get the first day of the month, I created a new DateTime variable called ‘StartDate’ and used the following expression:

(DT_DATE)(DT_DBDATE)DATEADD("dd", -1 * (DAY( GETDATE() )-1),GETDATE() )

Then I created a second DateTime variable called ‘myendDate’ to calculate the last day of the current month:

DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",1,GETDATE()))))

The variable myendDate comes out to 1/31/2013 11:59PM for the month of January and my StartDate is 1/1/2013.

Closer but still no cigar. I needed to change the date format to read yyyy-mm-dd in order for this to work properly in CAML. So I created a second variable called ‘myEndDate2’, set it as a String data type with a scope of Data Flow task.
Because all my bday and anniversary dates are within the 2012 calendar year, I manually hard code the year to 2012.
The expression used to change the date format for ‘myEndDate2’:

(DT_WSTR,4)  2012  + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::myendDate]), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DAY( @[User::myendDate]), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[User::myendDate]), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[User::myendDate]), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[User::myendDate]), 2)

The same principle applied to another string variable called ‘myStartDate2.’ I needed an expression that would evaluate the first day of the month using ‘yyyy-mm-dd hh:mm:ss’ format.

(DT_WSTR,4) 2012  + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
    +   "01" + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

PULLING IT ALL TOGETHER
Now that I have my dynamic expressions for mystartDate2 ( first day of the month) and myEndDate2 (last day of the month) , its time to apply them to the CAML query. In order to apply them in SSIS, you need to create a CAML expression in the Date Flow task. Under the properties of Data Flow, there is a section called Expressions.

Click the ellipsis (…) to open the expression builder dialog window. This is where you can code your CAML using the SSIS mystartDate2 and myEndDate2 variables.

"" +  (DT_WSTR,30  )   @[User::myStartdate2] + ""  +     (DT_WSTR,30  )   @[User::myEndDate2]  +
" "

I added my CAML expression and checked the syntax by clicking Evaluate Expression button. If the Evaluated value, appears, then your syntax is correct.

SENDING THE EMAIL
In the Control Flow I added a Script Task to send out the email. You can use either VB.net or C#. My code to generate the email was used below:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Xml
Imports System.Data.OleDb

 _
 _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim oleDA As New OleDbDataAdapter
        Dim dt As New DataTable
        Dim col As DataColumn
        Dim row As DataRow
        Dim sMsg As String

        oleDA.Fill(dt, Dts.Variables("sp_people").Value)

        Dim mynewMail As New MailMessage()
        Dim mySmtpClient As SmtpClient

        mynewMail.From = New MailAddress("#SharePointTeam@TradeStation.com", "Sharepoint Team")

        mynewMail.To.Add(New MailAddress("dweinberg@TradeStation.com", "SharePoint Team"))
        mynewMail.Subject = "Dates to Celebrate"

        Dim bdy As String
        Dim bInsertedGraphic As Boolean = False

        bdy = "    Happy Birthday/Anniversary    #outlook a{padding:0;} body{width:100% !important;}body{-webkit-text-size-adjust:none;}  body{margin:0; padding:0;}  img{border:none; font-size:14px; font-weight:bold; height:auto; line-height:100%; outline:none; text-decoration:none; text-transform:capitalize;} #backgroundTable{height:100% !important; margin:0; padding:0; width:100% !important;}h1, h2, h3, h4, h5, h6, ol, ul, li, img, span,strong {  margin: 0;  padding: 0;} img {border: none; }  a {border: none; } .style2{ height: 529px;} .style3{height: 17px;width: 546px;} .style4{width: 546px;}.style5 {width: 269px;} .style6 {width: 356px; height: 187px; }  <table width='600' border='0' align='center' cellpadding='10' cellspacing='0'><tr><td bgcolor='#b1ffad'><table width='580' border='0' cellpadding='0' cellspacing='0' bgcolor='white'>  <tr>  <td width='75'> </td> <td width='217' valign='top'><table border='0' cellspacing='0' cellpadding='0' style='width:558px;'>  <tr> <td class='style3'> </td> </tr> <tr> <td class='style2'> <span style='color:black;'>Celebrating this month...</span> <h1 style='font-size:38px;color:black;font-family:Tahoma, Times, serif;font-weight:bold;width:532px;padding:0 0 5px;'> Happy Anniversary to:</h1>  <h2 style='font-size:16px;color:black;font-family:Tahoma, Times, serif;font-weight:bold;padding-bottom:5px;width:532px;padding-left:20px;padding-right:0;padding-left:10;'> "

        For Each row In dt.Rows
            For Each col In dt.Columns

                If Not bInsertedGraphic And row(col.Ordinal).ToString().Contains("Birth") Then
                    bdy += "</h2><div align='center'><br><br><img class='style6' src='http://team.trad.tradestation.com/style%20library/images/happy-anniversary.png' /></div><br /><h1 style='font-size:38px;color:black;font-family:Tahoma, Times, serif;font-weight:bold;width:532px;padding:0 0 5px;'> Happy Birthday to:<h2 style='font-size:16px;color:black;font-family:Tahoma, Times, serif;font-weight:bold;width:532px;padding:0 0 5px 20px;'> "
                    bInsertedGraphic = True
                End If
                bdy = bdy &amp; row(col.Ordinal).ToString &amp; vbCrLf &amp; "<br>"
            Next
        Next

        bdy += "<br><br><div align='center'><img class='style6' src='http://team.trad.tradestation.com/style%20library/images/happy_bday.png' />   </div><br /> <span style='font-size:14px;color:black;font-family:Tahoma, Times, serif;line-height:18px;'>May every glowing candle on your cake be a wish that comes true.<br />Wishing you good health and happiness in life. </span><br /><br />The TradeStation Team</td></tr> <tr> <td height='15' valign='bottom'></td> </tr> </table></td> <td valign='top'></td> </tr> </table></td> </tr></table>"

        mynewMail.Body = bdy
        mynewMail.IsBodyHtml = True
        mySmtpClient = New SmtpClient("svc-smtp.trad.tradestation.com")
        mySmtpClient.Send(mynewMail)
    End Sub

End Class

DERIVED COLUMN EXPRESSIONS
MySpecialDate:

(DT_WSTR,30)(DT_DATE)MySpecialDate

Message:

REPLACE(Message,"string;#","") + " on " + (DT_WSTR,30)DATEPART("month",MySpecialDate) + "/" + (DT_WSTR,30)DATEPART("dd",MySpecialDate) + "/" + (DT_WSTR,30)DATEPART("yy",GETDATE())

VARIABLE EXPRESSIONS
StartDate:

(DT_DATE)(DT_DBDATE)DATEADD("dd", -1 * (DAY( GETDATE() )-1),GETDATE() )

myendDate:

DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",1,GETDATE()))))

myendDate2:

(DT_WSTR,4)2012 + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(@[User::myendDate]), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DAY( @[User::myendDate]), 2) + " "
+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[User::myendDate]), 2) + ":"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[User::myendDate]), 2) + ":"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[User::myendDate]), 2)
 

myStartDate2:

(DT_WSTR,4)2012  + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+   "01" + "
+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)
 

CAML EXPRESSION

"<Query><Where><And><Geq><FieldRef Name='MySpecialDate' IncludeTimeValue='TRUE' /><Value Type='DateTime'>" + (DT_WSTR,30  )   @[User::myStartdate2]     + "</Value></Geq><Leq><FieldRef Name='MySpecialDate' IncludeTimeValue='TRUE' /><Value Type='DateTime'>"  + (DT_WSTR,30  )   @[User::myEndDate2]     + " </Value></Leq></And></Where></Query>"