SSIS truncates and/or rounds the data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pravin - 27 Nov 2005 04:45 GMT

Hi,

The problem is decimal values gets truncated when passed from excel source
to data conversion object. Following is flow of SSIS package.

1. Source excel file, has a column amount.

2. Read the amount colum as text (imex=1 in connection string), as sometimes
it may have text data by mistake, which needs to be trapped.

3. When in SSIS designer, and I see data preview of excel file, all the
values are show correctly. e.g. 1234.12345678901 value in excel is shown same
in preview.

4. Next the value is given to conversion object to convert it to numeric
data type.

5. When I see data preview between excel connection and data conversion
object, I found that the vlaue is converted/truncated to 1234.1235. However,
I wish to retain the original value till all decimal places i.e.
1234.12345678901.

In nutshell, when preview of excel source it checked, it shows original
value retaining all decimal places. And then if we add a watch (data viewer)
between excel connection and conversion object, there the values are shown
truncated.

My questions are at which point values gets truncated, and why. How to
overcome this so as to retain the original values. Thanks in advance.

Signature

Regards,
MS Guy

Reply to this Message

Sophie Guo [MSFT] - 28 Nov 2005 06:56 GMT

Hello,

I tested the issue on my side but I didn't reproduce the issue. Can you
post here the detail steps to reproduce the issue?

For your reference, I tested the issue by performing the following steps:

1. Create a excel file which include the following data:

col data
1 1234.123456789010

The data type of the data column is Number which decimal places is 12.

2. Use SQL server Import and Export wizard to import data from the Excel
file into a SQL server database.

3. Choose the excel file as data source file. Check the "First row has
column names"

4. Choose the SQL server database as the Destination.

5. Select the "Copy data from one or more tables or views" option.

6. On the "Select Source Tables and Views" screen, check 'Sheet1$', click
the Edit button.

On the "Column Mappings" window, map the data column as float data type or
nvarchar(200) data type. The SQL statement looks like the following:

CREATE TABLE [test5].[dbo].[Sheet1] (
[col] float,
[data] nvarchar(200)
)

7. Follow the wizard to finish it. Execute the package.

It works fine on my side and the data is imported successfully. I hope the
information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message