December 18, 2002 at 10:34 pm
In DTS, I am using the Transform Data Task to move data from an Oracle view into a SQL server table. I can get data from Oracle tables fine. I can even set up the tranformations from the Oracle columns in the views to the SS table. When I do a preview from the source tab it consistently retrieves zero rows which is also what I get when I execute the transform. I have verified the ORACLE view works using SQLPlus and TOAD. Has anyone had similar problems? Is there a setting not set correctly? Or is this some nuance I will have to work around? Any help would be appreciated. Thanks.
Skip Gallagher
Quest Industries, Inc.
(479) 685-4546
Skip Gallagher
Quest Industries, Inc.
(479) 366-0789
December 20, 2002 at 2:21 pm
Hello.
Should work. The only suggesting I can give is try using the 'Microsoft ODBC Dirver for Oracle'
if you're not already. That was the only hangup I've had when tyring to pull from an
Oracle view.
Everett Wilson
ewilson10@yahoo.com
December 20, 2002 at 2:34 pm
I have seen issues similar to what you describe in a project before. It turned out to be conversion issues. You might check out the data types returned in the view, and check them against SQL's same types.
December 20, 2002 at 2:41 pm
I'd forgotten about that. If I remember correctly one possibility is the decimal value
missing either the 'Precission' or the 'Scale' value. Hope this helps.
Everett Wilson
ewilson10@yahoo.com
December 20, 2002 at 6:07 pm
I have had no issues. You obviously are missing something somewhere. However, I would suggest if you use any version of Oracle later than 7 do not use the MS drivers as you will run (often) into unsupported datatypes. I use the Ole Provider for Oracle from the Oracle client install. Just a note, it sometimes helps to rebuild a package from scratch and keep track of all the settings you made, this way you may see the mistake. Can you explain your package in detail to us, how connections are made, how the data is mapped, etc and we might have a better answer.
December 23, 2002 at 3:59 pm
Sure enough, a change in the drivers took care of the numeric data type issue.
For anyone who's intereted, the original problem appears to be related to Oracle
having thee Number data types and the source that I was pulling form was using the
Number(P) data type and SQL Server was having trouble figuring out the Scale value for it.
I wasn't able to find a really good explanation but I did find one article that helped at MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsm/html/oracle2sql.asp
Everett Wilson
ewilson10@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply