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. I have a few that also require me to compare it to data in a SQL Server database. 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 total) - 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 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) - The validation to compare the data from Access to data in the SQL Server database I use a Lookup Transformation and return the "Lookup No Match Output" to the object variable. - 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 is would not it be much simpler to have all the validation failed data into seperate staging tables? This way all I would be needing is TSQL code to fetch data from tables using XML PATH concatenation technique for all columns and do UNION ALLs(OR using a temp table since there would always be one column as output) for all the staging tables data if any. After that, sending a single email should not be an issue. Other benefits possible could be easier auditing, easier restart from any point etc. I hope I am making sense and not missing something important.