September 19, 2011 at 6:11 am
ACinKC (9/16/2011)
I've done things similar to this (importing mainframe data into SQL) using SSIS (and Business Objects too, for that matter) and it was very straightforward and easy. Is there a requirement that you do it using SQL rather than a data loading tool?
I am not familiar with this at all, how would I go about it?! Always good to learn new things etc.....
September 20, 2011 at 8:08 am
Unfortunately it's been a couple of years since I did that, and I'm in a totally different job and role, so the details are pretty much lost, but if I remember correctly, it went something like:
Read a line and scan for data patterns to identify the type (I seem to remember doing this in a script)
If it's a header, branch to the "header routine"
If it's a detail line, branch to the "detail routine"
If it's a footer, branch to the "footer routine" and terminate processing
The various "routines" would parse the line and move the data elements to the appropriate fields in the target table.
Edit: You could also check the length of the line to determine the type...detail lines are "long", footer lines are "short" and header lines are just right........
September 22, 2011 at 9:21 am
The Conditional Split Transformation in SSIS will make quick work (and by quick I mean quick to develop and perform quick too) of the file you are being asked to process. Could it be done in T-SQL, of course, that hammer will knock down the problem but SSIS will not bloat your transaction logs and blast your buffer pool the way doing all this heavy lifting in the database engine will.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply