3

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.

1 Answer 1

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.

3
  • That sounds all nice (especially the SSRS idea) however I am trying to keep this process to a minimum for maintenance purposes. This application and database is hosted in a different organization that also provides support. Although I would enjoy designing your suggestion it is a bit much for me to put into production in the current environment...
    – user507
    Apr 4, 2013 at 5:23
  • Also I added the one requirement your answer reminded me of... I did initially think of sending it all to a database (SQL Server) but with the package being called every 15 minutes and the potential for it to process more than one file at a time, I thought doing the table thing might get to congested. I would have to identify which file the records were associated to, and it is a bit more work than I would like to get into right now. As I am hoping this process will get moved to XML based data or something in the near future.
    – user507
    Apr 4, 2013 at 5:25
  • From my experience the minimal effort to set up SQL tables pays off even during your Unit Testing phase. You can easily write and reuse test SQL queries to check and compare your results at each iteration and for varying inputs. SQL has no problem handling multiple inputs and parallel executions - just dont lock the target tables. In that scenario I would be more worried about running out of RAM with your current design. SSRS is optional - once you have the data in tables you can use a wide range of tools to get it out.
    – Mike Honey
    Apr 4, 2013 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.