SSIS错误导入Excel日期（截断错误） [英] SSIS Error importing Excel Date (truncation error)

问题描述

我很抱歉发布一个很简单的问题，但我找不到答案，而我正在浪费时间（不仅仅是这几个小时）。我对SSIS很新，只是踢我的背部。

背景：

相当简单的SSIS包将Excel工作表导入SQL Server中的暂存表。因为我不想通过使用错误的命名法误导任何人，所以我将Excel的Excel源和SQL Server表作为目标表。

这个包以前曾经工作过。但是，由于日期列的数据截断，因此现在失败。 Excel列已格式化为DATE（并且我在DATE内尝试过几种不同的格式选项）。目标列也是DATE列（NOT datetime）。 Excel中的数据主要是具有少量偶发值的空单元格。我认为这些错误是在日期开始出现在数据中而不是空白的时候开始的。

我已经尝试使用双方的高级编辑器（Excel& Target ）并尝试了许多数据类型设置，但我仍然遇到同样的故障。我怀疑现在已经完成了我所做过的各种测试的麻烦了。

我还尝试为日期字段添加数据转换转换date [DT_DATE ] - 没有工作。 AND，我已经尝试创建一个派生列 - 首先基于Excel列，然后在转换列。所有这些尝试都失败了。

问题：

1）导入Excel数据的最佳做法是什么进入SQL Server for DATE列？

2）由于这是两个非常成熟的Microsoft Apps（Excel& SQL Server）协同工作，似乎应该很简单。这让我相信我一定在这里错过一些基本概念。任何人都可以直接给我吗？

3）你们如何在Excel中获得Excel日期？

4）编辑后同步列的技巧是什么？

感谢您提供的任何见解。很抱歉打扰所有人看起来很简单。

David

解决方案

p>从excel工作表导入数据时的主要问题是excel是Excel中的每个列都可以有多种数据类型或格式，因此同一列可以包含日期和数字以及不同格式的文本或日期（某些格式无法转换

如果所有日期值都存储为日期（而不是文本），则从excel工作表中导入日期的最佳做法是转换 DATE 到数字格式0.000000000 （在excel中它被称为串行日期时间） 从excel或以程序方式使用图书馆，如 Microsoft.Office.Interop.Excel

您可以参考这个链接，但使用以下内容：

  xlCells.NumberFormat =0.0000000
  

然后在SSIS包中使用脚本组件将其重新转换为Date，使用 DateTime.FromOADate（）功能

*假设 inColumn 是具有数字类型的Date列，添加一个输出列 outColumn 类型为 DT_DBTIMESTAMP 或 DT_DATE 并使用以下代码：

 如果不是Row.inColumn_IsNull然后
 
 Row.OutColumn = DateTime.FromOADate（CDbl（Row.inColumn））
 
 Else 
 
 Row.OutColumn_IsNull = True 
 
结束如果
  

注意：将列转换为数字格式时，忽略所有格式，但仍具有日期值

• to r详细了解Excel中的DateTimes，您可以参考此链接




• 要详细了解可以在SSIS中隐式转换为日期的日期时间格式，请遵循 SSIS源格式隐式转换日期时间

I am sorry to post what seems a very simple issue but I cannot find an answer and I am wasting days (not just hours at this point). I am fairly new to SSIS and it is just kicking my backside.

Background:

Pretty straightforward SSIS Package to import an Excel sheet into a Staging table in SQL Server. Since I do not want to mislead anyone by using the wrong nomenclature, I will refer to the Excel source as Excel and the SQL Server table as the Target Table.

This package HAS worked before. However, it is now failing because of data truncation for a Date Column. The Excel Column has been formatted as a DATE (and I have tried a few different format options within DATE). The target column is also a DATE column (NOT datetime). The data in Excel is predominantly empty cells with a few sporadic values. I think the errors started when the dates started appearing in the data (rather than just blanks).

I have tried using the Advanced Editor for both sides (Excel & Target) and tried numerous data type settings all around but I keep getting the same failure. I suspect that it is now pretty messed up with the various tests that I have done.

I have also tried adding a Data Conversion transform for the Date field "date[DT_DATE]" – that did not work. AND, I have tried creating a Derived Column - first based on the Excel column and then on the Transformed column. All of those attempts have failed.

Questions:

1) What is the best practice for importing Excel data into SQL Server for DATE Columns?

2) Since this is two very mature Microsoft Apps (Excel & SQL Server) working together, it seems like it should be simple. This leads me to believe that I must be missing some basic concepts here. Can anyone set me straight?

3) How do all of you get an Excel date into SQL Server?

4) What is the trick for synchronizing columns after making edits?

Thanks for any insights you can provide. Sorry to bother you all with what seems pretty simple.

David

解决方案

The main problem when importing Data from excel worksheets is that excel is that each column in excel can have multiple data types or formats, so the same column can contains Dates and Numbers and text or dates with differents formats (Some formats cannot be converted implicitly to dates in SSIS).

If all date value are stored as date (not Text), The best practice to Import dates from an excel worksheet is to convert DATE to Number format "0.000000000" (in excel it is called Serial DateTime) from excel or programmatically using a library like Microsoft.Office.Interop.Excel

You can refer to this Link but use the following:

xlCells.NumberFormat = "0.0000000"

Then in SSIS package use a script component to convert it again to Date using DateTime.FromOADate() Function

*Assuming that inColumn is the Date column with a numeric type, add an output column outColumn of type DT_DBTIMESTAMP or DT_DATE and use the following code:

If Not Row.inColumn_IsNull Then

    Row.OutColumn = DateTime.FromOADate(CDbl(Row.inColumn))

Else 

    Row.OutColumn_IsNull = True

End If

Note: When converting column to Number Format, you ignored all formats but still have the date value

• To read more about DateTimes in Excel you can refer to this Link

• To read more about Date time formats that can be implicitly converted to date in SSIS follow SSIS Source Format Implicit Conversion for Datetime

这篇关于SSIS错误导入Excel日期（截断错误）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！