Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Precision values not displayed in SQL server

Hi Experts,

I'm using SQL 2005 (SSIS - Integration services) to connect to Oracle R12 using Oracle Drivers. Data appears to be OK on both the Oracle and SQL server DB but on the SQL server Database, precision values are not displayed.

Example,
I have a $94.50 on a particular DB view in Oracle
When queried the same table in SQL server 2005 (SSIS), the same value is shown as $94

Can you help?
0
legolasthehansy
Asked:
legolasthehansy
1 Solution
 
Jared_SCommented:
So if you query the sql server database directly, without the use of SSIS, the value would show up as $94.50?
0
 
legolasthehansyAuthor Commented:
My DBA says he cannot query it without the SSIS.
0
 
Saurabh BhadauriaCommented:
check the datatype of column where you are storing your decimal value,  also check datatype is not getting change with in dataflow after fetching data from oracle.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
slightwv (䄆 Netminder) Commented:
>>My DBA says he cannot query it without the SSIS.

Then how do you know you have "94.50 on a particular DB view in Oracle"?

I agree this is likely a data type issue but I've not used SSIS.
0
 
Jared_SCommented:
Basically, here is what could be going wrong.

1) An MS SSIS driver for an oracle database could be causing you trouble.
2) The problem could be in SSIS with the way the data is being pulled or handled during transition.
3) The SQL Server database could have an incorrect datatype.

You need to start isolating each potential cause to either identify it or rule it out as the cause of your problem.

You'll have to work with your DBA to get most of this foot work done.

I think you'll find this forum a lot more productive once the problem is narrowed down a little.

I hope this ends up being something simple, and that this is the biggest fire you have to put out this week!
0
 
legolasthehansyAuthor Commented:
Thanks Jared_S and everyone - I got some more info from the DBA.

1) We're not using MS drivers.  We're using the one issued by Oracle.  The version is 11g.  
2) I've tried various conversions.  The problem is the cents (or .50 in the example) are being dropped before they can get to even the next step.  They are being dropped in the OLE DB Source.
3) I've tried various data types: money, float, and numeric.  But again, keep in mind what I said in #2 - cents are being dropped before it's written to the database.
0
 
Jared_SCommented:
That's good information (but bad)... so much for that being an easy fix.

Is this a one time migration?

I wonder if it wouldn't be simpler to dump the data to a csv or txt file and import it from there. I know it's a complete work-around, but it gets you moving.

In the meantime, I'll be another set of eyes on the web for you. I've never experience this problem directly, but surely someone else out there has been through this and wrote about it.
0
 
Jared_SCommented:
legolasthehansy,

Ask the DBA to go into the advanced editor of the OLEDB data connection in the ssis package
and change the data type of affected field to DT_R8 (double precision floating integer)

I think that might resolve this for you.

http://msdn.microsoft.com/en-us/library/ms141036.aspx

You may find some of the suggestions in this post helpful too if that doesn't resolve your problem.

http://bidn.com/forums/microsoft-business-intelligence/integration-services/638/oracle-data-source-input-in-ssis
0
 
legolasthehansyAuthor Commented:
Update from the DBA -
Setting the OLE DB Source Output money columns explicitly to currency [DT_CY] in the OLE DB Source's Advanced Editor subscreens fixed the issue

Thanks Jared_S!!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now