May 27, 2011 at 4:15 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 10:43 am
You may want to post this question in the SSIS Forum as was suggested in your other forum:
http://www.sqlservercentral.com/Forums/Topic1116550-391-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 30, 2011 at 6:08 pm
kvacola (5/27/2011)
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!
This appears to be a text case for SSIS error handling, I would start by checking: http://msdn.microsoft.com/en-us/library/ms141679.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply