August 21, 2012 at 11:23 am
The first part is easy. SSIS can do a For Each loop over the files in a directory, and it's easy to get it to import all of them into a staging table. If you Bing/Google "ssis import multiple files", you'll find numerous tutorials, walk-throughs, articles, discussions, etc., on that subject. Start with MSDN. Shouldn't take long to get that up and running.
If you run into bugs/questions on that stage, feel free to ask here. SSIS is harder to debug on a forum than T-SQL, because you can't just copy-and-paste code, but we can usually help anyway.
On the data cleanup, is it necessary to do all that inside the XML? It will be much, much easier to stage the XML into a table with an XML datatype column, then extract the data into relational format, clean it up in tables, and then rebuild it into XML if you need it back in that format. Will that work for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply