SQL Server Integration Services (SSIS) – Using Send Mail Task for data validation checks

What I wanted to do was to be notified in my data if there were not enough rows of data, or if the financials were too low, indicating that there was a potential issue with the data coming in.

And if it was too low to fire off an email. If it was correct then to not send an email. SO that we would only be notified when there is an issue.

So in order to explain this I will explain it with an example below, which will make it easier to understand.

Example:

·         We want to compare the count of SQL Server Analysis (SSAS) Partitions for our Internet Sales Cube.

·         If the count of partitions increases then send an email.

·         If the count of partitions remains the same then do nothing.

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

You can refer to my previous blog post if you want to see how we populate the SSAS Partition Details

                PUT IN HERE

NOTE: We are using SQL Server 2014 and SQL Server Data Tools with Visual Studio 2013

1.       Create a new SSIS Package.

2.       Next we create the following 2 variables, which will hold our Previous and Current Partition Counts

a.       

3.       Next we will load the data into our variables using an Execute SQL Task and a SQL Server TSQL Script.

a.        NOTE: I normally would put the TSQL script into a stored procedure, but for this example I am going to put it in the package so that you can see how it got populated.

b.       From the SSIS Toolbox drag in an Execute SQL Task, then configure it with the following:

                                                               i.      As you can see we gave it a name, changed the result set to Single Row and created our connection

                                                              ii.       

c.        Then click on the Ellipses next to SQLStatement and put in the following TSQL code.

Declare @PreviousCountOfSSASPartitions asint

Set @PreviousCountOfSSASPartitions =

       (

         Selectcount(1)

         FROM [AdventureWorksDW2012].[dbo].[Mart_TD_SSAS_PartitionDetails] with (nolock)

         where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’

         andconvert(date,CreatedTimeStamp)=‘2013-07-09’

       )

Declare @CurrentCountOfSSASPartitions asINt

Set @CurrentCountOfSSASPartitions =

       (

         Selectcount(1)

         FROM [AdventureWorksDW2012].[dbo].[Mart_TD_SSAS_PartitionDetails] with (nolock)

         where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’

         andconvert(date,CreatedTimeStamp)>=‘2013-07-09’

       )

Select   @PreviousCountOfSSASPartitions as PreviousCountOfSSASPartitions

        ,@CurrentCountOfSSASPartitions as CurrentCountOfSSASPartitions

                                                               i.      Then click Ok.

d.       Now in the left hand pane click on Result Set and configure it so that we can populate our variables as shown below:

                                                               i.     

e.       Then click Ok.

4.       Next we are going to create a Send Mail Task so that when we get an error to send out the email.

a.        We dragged in a Send Mail Task and configured it to send via our Mail Server.

b.       Along with who to send it to, as well as the details in the email.

c.        NOTE: You can add expressions to add what you would to send dynamically.

                                                               i.      If you are going to put something in the actual email message, it is easiest to compile it in Notepad and then copy and paste it. This way it will keep your content as expected.

5.       Now we are going to use the precedence constraint in order to decide if we must send the email or now.

a.        Drag your precedence constraint from your Populate variables for Count of Partitions to our send mail task

                                                               i.     

b.       Then right click on the Success Precedence Constraint and select Edit

c.        We then configured it with the following as shown below

d.      

e.       For the Expression we configured it saying if the Previous Partition Count is less than the Current Partition Count then continue and send the email.

@[User::PreviousCountOfSSASPartitions] <  @[User::CurrentCountOfSSASPartitions]

f.         Then click Ok, and Ok again.

g.        You will now see that we have put in an expression

                                                               i.     

6.       Now when we run it when the partition counts are different it will send the email as shown below

a.       

7.       And then if we run it when the Partition counts are the same, it will NOT go to the send Mail Task

a.