SSIS Design consideration with object variables

Question

I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation checks are simply running particular queries against the Access database and if records are returned, the validation failed for that check.

My requirements:

• I am pulling the list of Access DBs to validate from a SQL Server database.
• I iterate through the list doing the validation checks (about 36 tot
• The records returned will vary on column count and data type.
• I need to take only those records returned and generate a single email.
• A title or heading needs to accompany each recordset in the email in order to associate the records with the validation check

EDIT: One additional requirement that might affect design: this package could will be called at minimum every 15 minutes.

Example:

Validation 1 errors
Column1 Column2
cv1     cv1
cv2     cv2

What I have setup so far:

• Data Flow pulls the list of Access files to validate into an object variable
• Foreach loop iterates through each record (using ADO Enumerator), mapping each column to a variable Script task to validate the file is accessible (failures > Execute SQL Task updates database to skip the file)
• Data Flow is setup for each validation in order to query the Access file(s)
• Each Data flow is a OLE DB Source to a Records Destination I have each validation populating a object variable

I am populating an object variable for each validation. Since the column count can vary I did not see an option to send it to anything else; at least with my knowledge of SSIS.

What I have setup seemed good when I started but... I am now sitting here thinking the best way to check all these object variables for records and put in an email. I came across Andy Leonard's blog post on access the DataSet in an object variable. Then another blog post on retrieving data from the Recordset Destination.

Anyone have suggestion(s) on doing this differently or how to build that email with all these object variables?

My first thought would be to create a second foreach loop to iteration through each object variable. Grabbing each recordset and appending a string variable to contain each table, formatted as HTML or something to make it look pretty in the email.

Answer 1

I would replace your output Recordset Destinations with OLE DB Destinations and deliver the required data into database tables. As well as making the remaining work easier, this could also give you a far more useful Audit/History of previous runs.

From that point I would then write SSRS reports against those tables and use SSRS subscriptions to prepare well formatted emails and deliver them.