Thursday, January 29, 2009

SSIS 2005 : Staging data in RAW Files

I'm reading: SSIS 2005 : Staging data in RAW FilesTweet this !
In SSIS 2005, RAW File is one of the fastest way to stage data extracted from any source system. Also there's a corresponding source adapter available to read data from the same. We used this mechanism in one of our projects, and considered RAW File as the staging mechanism for a huge number of extracts which were of course delta based. This mechanism works fine and faster, but the real trouble is visible after the extract is dumped in the RAW file. There are two issues now:

  1. Firstly there's no tool available out of the box to read the same. A package that reads from a RAW File and dumps in to a text file, needs to be developed. Luckily we have a tool like RAW File reader that can read the RAW file. To download the tool go to
  2. The biggest issue is, if the schema at the OLTP level changes; then you are ready for the big bang. When the load reads from the raw files that are created from the updated schema, the RAW File Source Adapter immediately fails. This component stores the schema in the package when it reads the schema for the first time. Also the DelayValidation property is of no use as this adapter does not support reading of the schema dynamically. Effectively your loads would fail, and require modification.

My experience and finding is that, think twice before considering RAW file as the staging mechanism; especially if there's a scope that your source system schema might undergo a level of change.

No comments:

Related Posts with Thumbnails