Generating Alert Emails with Dynamic Attachments using SSIS

A couple months back a colleague asked me if there was an easy way to send email alerts to defined users when a stored procedure returned a non-empty result set, easy right? Well thats what I thought, then the real fun started, turns out that there were lots of stored procedures with different columns and datatypes going to different people. After some head scratching and googling a light bulb went off, and a solution started to take shape.

– Use a SQL Table to store the Meta Data, SP_Name, email address, file name

– Use SSIS to iterate through the metadata

– use a script task to create excel documents using excel automation (worked in BIDS or SSDT but package failed when executed from SQL Server so with the help of this awesome post, Creating-basic-Excel-workbook-with-Open-XML, I was able to use the Open XML SDK to generate the Excel docs.)

– save the documents to a file share

– use a script task to email attachments to users.

What follows is a how I did it.

Couple prerequisites:

1. You will need the Open XML sdk which can be found here

2. As you are reading my blog I assume that you have either BIDS 2008 R2 or SQL Server Data Tools (SQL 2012) installed, however one of the two is required

3. The following database objects: table to store meta data and stored procedures to call. Below you will find a script to create the SQL objects that I used in to validate the solution

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_DummyProc1]
as
BEGIN
select top 10 users.UserID,users.UserName, users.UserType, users.AuthType from ReportServer.dbo.Users
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DummyProc2]    Script Date: 31/05/2012 7:10:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DummyProc2]
as
BEGIN
select top 10 Subscriptions.SubscriptionID from ReportServer.dbo.Subscriptions
END
GO
/****** Object:  Table [dbo].[MyTest]    Script Date: 31/05/2012 7:10:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTest](
	[myKey] [int] NOT NULL,
	[myValue] [int] NULL,
	[RV] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[myKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[t_StoredProceduresToCall]    Script Date: 31/05/2012 7:10:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_StoredProceduresToCall](
	[procedureKey] [int] IDENTITY(1,1) NOT NULL,
	[storedProcName] [varchar](200) NULL,
	[emailAddress] [nvarchar](100) NULL,
	[fileName] [nvarchar](100) NULL,
 CONSTRAINT [PK_t_StoredProceduresToCall] PRIMARY KEY CLUSTERED
(
	[procedureKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MyTest] ([myKey], [myValue]) VALUES (1, 2)
GO
INSERT [dbo].[MyTest] ([myKey], [myValue]) VALUES (2, 2)
GO
SET IDENTITY_INSERT [dbo].[t_StoredProceduresToCall] ON

GO
INSERT [dbo].[t_StoredProceduresToCall] ([procedureKey], [storedProcName], [emailAddress], [fileName]) VALUES (1, N'sp_DummyProc1', N'someone@somewhere.com', N'excelFile')
GO
INSERT [dbo].[t_StoredProceduresToCall] ([procedureKey], [storedProcName], [emailAddress], [fileName]) VALUES (2, N'sp_DummyProc1', N'someone@somewhere.com', N'excelFile1')
GO
SET IDENTITY_INSERT [dbo].[t_StoredProceduresToCall] OFF
GO

Initial Plumbing

  • Create new Integration Services Project


  • Create the following package scoped variables:
Name Data type Value Expression
attachments String
directory String C:\Folder\
emailAddress String someone@somewhere.com
emailAddresses Object System.Object
filename String
fileNames Object System.Object
getStoredProcQuery String select StoredProcName,[fileName] from [dbo].[t_StoredProceduresToCall]
where emailAddress = ‘someone@somewhere.com’
“select StoredProcName,[fileName] from [dbo].[t_StoredProceduresToCall]
where emailAddress = ‘” + @[User::emailAddress] + “‘”
storedProcName String
StoredProcResults Object System.Object
  • Create an ADO.Net Connection Manager, pointing to the db where sql objects exist, name it adonet

Create a Query that returns a distinct list of email addresses the results will be used in our outer loop

  • Drag a SQL Task onto the Control Flow surface, call it GetEmailAddresses
    • Edit the task
    • Set the ResultSet to Full Result Set
    • Set the connection type to ADO.NET
    • Select the adonet connection
    • Set the SQL Statement to
    • SELECT distinct emailaddress from t_StoredProceduresToCall


    • Click on the Result Set Item in the Navigation list on the left
Click Add and Map Result Name 0 to the User:emailAddresses variable

Create the Outer Loop to send results to distinct email addresses

  • Drag a Foreach loop Container onto the Control Flow surface, call it Foreach Email
  • Connect the GetEmailAddresses sql task
    • Open the editor and set the enumerator to Foreach ADO Enumerator
  • Select the User::emailAddresses variable for the ADO source object variable


  • Select Variable Mappings and map the User::emailAddress variable to index 0


Get the Stored Procedures to evaluate by user

  • Drag a execute sql task into the Foreach Email container, call it Get Stored Procedures by User
    • Edit the task
  • Set ResultSet to Full Result Set
  • Set ConnectionType to ADO.NET
  • Set Connection to adonet
  • Set SQLSourceType to Variable
  • Set SourceVariable to User::getStoredProcQuery


  • Click on Result Set
  • Click Add and assign User::StoredProcResults to Result Name 0


Create the Inner Loop to generate the Excel Files by email address

  • Drag a Foreach loop Container into the Foreach Email container, call it Foreach SP by email
  • Connect the Get Stored Procedures by User sql task
    • Open the editor and set the enumerator to Foreach ADO Enumerator
  • Select the User::StoredProcReults variable for the ADO source object variable


  • Select Variable Mappings
    • map the User::storeProcName variable to index 0
    • map the User::fileName variable to index 1


Generate Excel Files and Save to file System

  • Drag a Script Task into the Foreach SP by email container, call it Generate Excel Files
    • Edit the task
  • Set the ReadOnlyVariable to
    • User::directory
    • User::emailAddress
    • User::filename
    • User::storedProcName
  • Set the ReadWriteVariables to
    • User::attachment


  • Click Edit Script and add the following references to the project
  • I am assuming that you are somewhat familiar with SSIS Script tasks and feel comfortable within the VSTA environment
    • DocumentFormat.OpenXml
      • C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll
    • System.Xml.Linq
      • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Xml.Linq.dll
    • WindowsBase
      • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\WindowsBase.dll


  • Add the following using declarations to the code
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Xml.Linq;
    using System.Linq;
    using System.Data.SqlClient;
    
  • Replace the Main() Method with the following Code
    • the code 
    • executes the stored procedure found in the User::storedProcName variable
    • if the SP returns any rows
      • a unique directory is created
      • The CreateExcelWorkbook method is called which builds a new excel workbook dynamically based on the DataReader passed into the function and saves the file
      • File location is added to the User::attachements variable
     public void Main()
            {
    
                using (SqlConnection conn = (SqlConnection)Dts.Connections["adonet"].AcquireConnection(null))
                {
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.CommandText = Dts.Variables["User::storedProcName"].Value.ToString();
    
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    SqlDataReader reader = comm.ExecuteReader();
                    try
                    {
                        if (reader.HasRows)
                        {
                            try
                            {
                                string szFileName = createDirectory() + "\\" + Dts.Variables["User::fileName"].Value.ToString() + ".xlsx";
                                CreateExcelWorkbook(szFileName, reader);
    
                                Dts.Variables["User::attachments"].Value = Dts.Variables["User::attachments"].Value.ToString() + "|" + szFileName;
                            }
                            catch
                            {
                                Dts.TaskResult = (int)ScriptResults.Failure;
                            }
                        }
                    }
                    catch
                    {
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                    finally
                    {
                        Dts.TaskResult = (int)ScriptResults.Success;
                        reader.Close();
                    }
                }
            }
    
  • Add the following Methods
    • These methods are what do all of the hard work, I made some mods to generate the rows and columns but all in all the author of Creating-basic-Excel-workbook-with-Open-XML, Mika Wendelius deserves all of the credit for providing a great tutorial and the code to back it up.
       private string createDirectory()
            {
                //Create Folder for email recipient
                string newPath = System.IO.Path.Combine(Dts.Variables["User::directory"].Value.ToString(), Dts.Variables["User::emailAddress"].Value.ToString());
                System.IO.Directory.CreateDirectory(newPath);
                // Create the subfolder
                newPath = System.IO.Path.Combine(newPath, System.Guid.NewGuid().ToString());
                System.IO.Directory.CreateDirectory(newPath);
    
                return newPath;
            }
    
     public static SpreadsheetDocument CreateWorkbook(string fileName)
            {
                SpreadsheetDocument spreadSheet = null;
                SharedStringTablePart sharedStringTablePart;
                WorkbookStylesPart workbookStylesPart;
    
                try
                {
                    // Create the Excel workbook
                    spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false);
    
                    // Create the parts and the corresponding objects
                    // Workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();
                    spreadSheet.WorkbookPart.Workbook.Save();
    
                    // Shared string table
                    sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart();
                    sharedStringTablePart.SharedStringTable = new SharedStringTable();
                    sharedStringTablePart.SharedStringTable.Save();
    
                    // Sheets collection
                    spreadSheet.WorkbookPart.Workbook.Sheets = new Sheets();
                    spreadSheet.WorkbookPart.Workbook.Save();
    
                    // Stylesheet
                    workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart();
                    workbookStylesPart.Stylesheet = new Stylesheet();
                    workbookStylesPart.Stylesheet.Save();
                }
                catch (System.Exception exception)
                {
                    MessageBox.Show(exception.Message);
                }
    
                return spreadSheet;
            }
    
            public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name)
            {
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild();
                Sheet sheet;
                WorksheetPart worksheetPart;
    
                // Add the worksheetpart
                worksheetPart = spreadsheet.WorkbookPart.AddNewPart();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
                worksheetPart.Worksheet.Save();
    
                // Add the sheet and make relation to workbook
                sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                {
                    Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
                    Name = name
                };
                sheets.Append(sheet);
                spreadsheet.WorkbookPart.Workbook.Save();
    
                return true;
            }
            public void CreateExcelWorkbook(string workbookName, SqlDataReader reader)
            {
                DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet;
                DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet;
                spreadsheet = CreateWorkbook(workbookName);
                if (spreadsheet == null)
                {
                    return;
                }
                AddWorksheet(spreadsheet, "Error Records");
                worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
    
                int fieldCount = reader.FieldCount;
    
                uint iterator = 1;
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    SetCellValue(spreadsheet, worksheet, iterator, 1, CellValues.String, reader.GetName(i).ToString());
                    iterator++;
                }
                iterator = 1;
                uint row = 2;
                while (reader.Read())
                {
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        SetCellValue(spreadsheet, worksheet, iterator, row, CellValues.String, reader.GetValue(j).ToString());
                        iterator++;
                    }
                    row++;
                    iterator = 1;
                }
    
                worksheet.Save();
                spreadsheet.Close();
            }
            private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value)
            {
                DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild();
                DocumentFormat.OpenXml.Spreadsheet.Row row;
                DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
                DocumentFormat.OpenXml.Spreadsheet.Cell cell;
                DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
                DocumentFormat.OpenXml.Spreadsheet.Columns columns;
                DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
                string cellAddress = ColumnNameFromIndex(columnIndex) + rowIndex;
                // Check if the row exists, create if necessary
                if (sheetData.Elements().Where(item => item.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements().Where(item => item.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
    
                    for (uint counter = rowIndex - 1; counter > 0; counter--)
                    {
                        previousRow = sheetData.Elements().Where(item => item.RowIndex == counter).FirstOrDefault();
                        if (previousRow != null)
                        {
                            break;
                        }
                    }
                    sheetData.InsertAfter(row, previousRow);
                }
    
                // Check if the cell exists, create if necessary
                if (row.Elements().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
                {
                    cell = row.Elements().Where(item => item.CellReference.Value == cellAddress).First();
                }
                else
                {
                    // Find the previous existing cell in the row
                    for (uint counter = columnIndex - 1; counter > 0; counter--)
                    {
                        previousCell = row.Elements().Where(item => item.CellReference.Value == ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
                        if (previousCell != null)
                        {
                            break;
                        }
                    }
                    cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress };
                    row.InsertAfter(cell, previousCell);
                }
    
                // Check if the column collection exists
                columns = worksheet.Elements().FirstOrDefault();
                if (columns == null)
                {
                    columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
                }
                // Check if the column exists
                if (columns.Elements().Where(item => item.Min == columnIndex).Count() == 0)
                {
                    // Find the previous existing column in the columns
                    for (uint counter = columnIndex - 1; counter > 0; counter--)
                    {
                        previousColumn = columns.Elements().Where(item => item.Min == counter).FirstOrDefault();
                        if (previousColumn != null)
                        {
                            break;
                        }
                    }
                    columns.InsertAfter(
                       new DocumentFormat.OpenXml.Spreadsheet.Column()
                       {
                           Min = columnIndex,
                           Max = columnIndex,
                           CustomWidth = true,
                           Width = 9
                       }, previousColumn);
                }
    
                // Add the value
                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
                if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
                {
                    cell.DataType = new DocumentFormat.OpenXml.EnumValue(valueType);
                }
                worksheet.Save();
    
                return true;
            }
    
            public static string ColumnNameFromIndex(uint columnIndex)
            {
                uint remainder;
                string columnName = "";
    
                while (columnIndex > 0)
                {
                    remainder = (columnIndex - 1) % 26;
                    columnName = System.Convert.ToChar(65 + remainder).ToString() + columnName;
                    columnIndex = (uint)((columnIndex - remainder) / 26);
                }
    
                return columnName;
            }
    

Email the files

  • Drag a Script Task into the Foreach Email container, call it Email Files
  • Edit the task
  • Set the ReadOnlyVariable to
    • User::emailAddress
  • Set the ReadWriteVariables to
    • User::attachment
  • Click Edit Script and add the following references to the project
  • Add the following using declarations to the code
    using System.Net;
    using System.Net.Mail;
    
  • Replace the Main() Method with the following Code
    • the code executes the stored procedure found in the User::storedProcName variable
    • if the SP returns any rows
      • a unique directory is created
      • Excel File is generated and saved
      • File location is added to the User::attachements variable
    • You will need to modify the following for this actually send emails
      • Replace from@somewhere.com with a valid from address in the creation of the MailMessage object
      • Replace smtpserver with a valid smtp server address in the creation of the SmtpClient object
      • Replace username and password with a valid values in the creation of the NetworkCredential object
      public void Main()
            {
                try
                {
                    string attachments = Dts.Variables["attachments"].Value.ToString();
                    Dts.Variables["attachments"].Value = "";
                    MailMessage emailMessage = new MailMessage("from@somewhere.com", Dts.Variables["emailAddress"].Value.ToString());
                    attachments = attachments.TrimStart('|');
                    string[] attachmentArray = attachments.Split('|');
                    for (int i = 0; i < attachmentArray.Length; i++)
                    {
                        Attachment a = new Attachment(attachmentArray[i]);
                        emailMessage.Attachments.Add(a);
                    }
                    emailMessage.Subject = "You have errors to deal with see attached documents";
                    // TODO: Add your code here
    
                    SmtpClient mySmtpClient = new SmtpClient("smtpserver");
                    mySmtpClient.Credentials = new System.Net.NetworkCredential("username","password");
    
                    mySmtpClient.Send(emailMessage);
    
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                catch
                {
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
    

    Executing the package should result in email(s) being generated with attachments based on the result set returned by the stored procedures.
    Hope this helps a couple people out.

Advertisements

One thought on “Generating Alert Emails with Dynamic Attachments using SSIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s