SSIS Excel数据导入-混合数据类型。

I am trying to import excel data using SSIS pkg but it is changing data type and converting to some other format with decimal.

我正在尝试使用SSIS pkg来导入excel数据，但是它正在改变数据类型并将其转换为其他的十进制格式。

I pull data from a set range

我从一个集合范围中提取数据

Example: Range A5:AB20

例如:A5范围:AB20

My data Column A5 (M)= 1.2

数据列A5 (M)= 1.2

SSIS Import something like 120000.001237

SSIS导入类似于120000.001237的东西

I was SSIS to import A5 = 1.2

我是SSIS导入A5 = 1.2

I tried this but am failing...

我试过了，但失败了……

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ABCDPrograms.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";

数据来源=C:\用户ABCDPrograms.xlsx;扩展属性="EXCEL 12.0 XML;HDR=YES;IMEX=1";

Original Data is imported from some legacy system and I cannot make changes there.

原始数据是从一些遗留系统中导入的，我无法在那里进行更改。

Kindly direct. Thank you

请直接。谢谢你！

1 个解决方案

#1

You need to use a Derived Column transformation in your dataflow task between the Excel spreadsheet and the database:

您需要在Excel电子表格和数据库之间的dataflow任务中使用派生的列转换:

Excel file, I know you say your spreadsheet is showing 1.2 but I can't see what's in the cell, so it does look like the larger value is what is held in the cell:

Excel文件，我知道你说你的电子表格显示的是1。2但是我看不出单元格里有什么，所以看起来更大的值是单元格里的值:

Derived transformation task, rounds the value (divided by 100000) to 1 decimal place, replacing the column value:

派生转换任务，将值(除以100000)四舍五入到小数点后1位，替换列值:

Resulting database entry, this column's datatype is set to DECIMAL(8,1) or however many preceeding digits you need):

由此得到的数据库条目，本列的数据类型被设置为十进制(8,1)或您需要的前位数):

Hope that helps

希望这有助于

#1