May 27, 2011 at 4:14 pm
Hello all ... first off SSIS n00b warning!
I am trying to import about 38+ Million rows from an Oracle RDB database to SQL. I have the ODBC connection made and I've tested the import for small chunks of data - which work just fine (mostly). Problem is there is column in the source data which is type = "REAL", and it imports to SQL as "Real, null" ... which is fine ... but occasionally the basic import/export utility will encounter errors with this "REAL" column, which then stops the whole dang process. BCP doesn't work because it encounters the same problem.
What I need to do is setup a custom SSIS package that either:
(a) Converts the incoming datatype from "REAL" to something like nvarchar(128). The incoming data is a decimal value, and I figure that sometimes I get ones that are just so small (or have so many trailing zeros) that the import utility can't automatically import them as REAL - or that they just may not be actual "REAL" values.
(b) Detects the row in error, spits it out to a error log file, and then continues to process the data.
I have but a basic knowledge of SSIS, and even that sets me up as the "office expert" in my group. I've got the SSIS book from Wrox on order, and in the meanwhile I've been scouring the interwebs for any clues on how to do either of the above.
So, any help, tips, tricks or pointers to other resources would be most appreciated. I have literally spent the past day searching for info on how to do this. I can't be the only one to do this, right?
Thanks in advance!
May 28, 2011 at 2:51 am
Why can't you use BCP together with a format file to change the REAL column into VARCHAR(128) (not NVARCHAR, since I wouldn't expect unicode data in a REAL column)?
May 30, 2011 at 8:50 am
Format file with BCP ... hmm. Didn't think of that. I'll have to lookup the details and try it out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply