ETL to Reporting Project

• AlexF1

  Old Hand

  Points: 384

  More actions

  June 7, 2010 at 8:45 am

  #222469

  Hi all,

  I’m working on a project with the following requirements;

  1.Copy Data from an ODBC source via SSIS daily, the ODBC connections points to a proprietary database to which we have no other access. There will be a complete reload each day, ALL DATA WILL BE DELETED AND RELOADED. (a load should take less than 2hrs for the foreseeable future).

  2.Perform validations on this data, these could be as simple as length/data type or more complex, (such as Value in Field A valid if Value in Field B = X) Error records should be captured for review.

  3.The data will then be reported on via SSRS through Stored Procedures.

  A natural solution would be Staging to Production to Reporting (three databases) but I’m not sure this is necessary, please see questions below.

  Questions

  Do I need a Staging database? If the Validation/Error handling in SSIS 2008 is good enough I should be able to send rows that will “fit” to their target tables and those that won’t to loosely typed error tables or maybe flat files for review.

  Bearing in mind the data will be cleared and reloaded every night would it make sense to have a Current and To Be Populated DB, one would always server Apps until and successful population of the other, this would give good availability.

  Would it be better to report directly off one the two above mentioned Databases rather than having a dedicate reporting database, (to scale up you would duplicate the above DB’s on x number of servers).

  These database are not that large in terms of data but have 200+ tables, we are a small team so my reason for wanting to minimise the number of databases is ease of maintenance.

  Any thoughts or ideas would be much appreciated!

  Thanks

  Alex