I'm new to SSIS but am fairly experienced with SQL Server (as a developer, not as a DBA). I'm using SQL Server 2005, Management Studio, and VS-2005 Business Intelligence Development Studio (BIDS).
I have a collection of externally sourced XML files that I want to eventually get into our database. While it hasn't yet been completely specified what I will need to do, I know that there may be some integrity testing on the data, and I will definitely
need to handle errors (in the data as well as various runtime errors such as network failure and the like).
The game plan right now is to make a small "sourcing DB" and put the data from the XML files pretty much directly into it, then take it from there. I suppose it might be better from a performance point of view to handle everything "on the fly" and avoid
such staging tables, but this is probably easier and I would guess more scalable as well. (Most of the files are small, < 100kB, but one is much bigger and may be a few hundred megabytes).
So my first question is this: Are there any compelling reasons why we should *not* perform this staging step and import the XML data as-is before attempting any cleansing/validation/data error handling? If so, I would appreciate a short explanation
of why and alternative suggestions - or, of course, links to the same.
View Complete Post