在SSIS中从CSV导入yyyyMMdd日期 [英] Importing yyyyMMdd Dates From CSV in SSIS

问题描述

我有12列使用 yyyymmdd 格式.在数据流任务中，我有一个平面文件源，一个派生列任务和一个 OLE DB目标 .我将以下表达式应用于派生列任务中的这些字段:

I have 12 columns using the yyyymmdd format. In the Data Flow Task, I have a Flat File Source, a Derived Column Task and an OLE DB Destination. I'm applying the following expression to these fields in the Derived Column Task:

(DT_DBDATE)(SUBSTRING((DT_STR,10,1252)([Date_Column]),1,4) + "-"
+ SUBSTRING((DT_STR,10,1252)([Date_Column]),5,2) + "-"
+ SUBSTRING((DT_STR,10,1252)([Date_Column]),7,2))

它一直使我在将其子字符串化之前转换字段，但是在 Connection Manager 中将字段设置为 DT_STR .在SQL Server中，目标字段的格式为 DATE . SSIS始终显示由于潜在的数据丢失而无法转换"错误.我已经检查了CSV中的原始数据，但看不到任何看起来不合适的日期.一切都符合格式.

It keeps making me convert the field before I substring it, but I have the fields set up as DT_STR in the Connection Manager. The destination field is in DATE format in SQL Server. SSIS always shows the "could not be converted because of a potential loss of data" error. I've checked the raw data in the CSV and don't see any dates that look out of place. Everything fits the format.

我还尝试按照建议的

I've also attempted to use a Data Conversion task as suggested here, but ended up getting the same kind of error. Any recommendations? The answers I've seen on here all point to the use of a Derived Column task, but several others I've mentioned this to have had the same problem I'm having.

这是数据示例.在所有错误中特别提到的字段是CRTFCTN_DT.数据集中有470个字段，但这确实显示了前两个日期字段.

Here's a sample of the data. The field specifically mentioned in all of the errors is CRTFCTN_DT. There are 470 fields in the dataset, but this does show the first two date fields.

解决方案::在连接管理器中，我将 yyyymmdd 字段更改为DT_STR(10)(日期为8个字符，日期为2个字符). (添加的破折号)，并重建了我的数据流任务.这样就解决了问题.如果您不重建任务，则 Derived Column 任务将不会使用新的数据类型.重新创建任务后，我也不再需要在应用子字符串之前将日期转换为字符串.

SOLUTION: In the Connection Manager, I changed the yyyymmdd fields to DT_STR(10) (8 characters for the date, 2 for the added dashes), and rebuilt my Data Flow Task. This solved the problem. If you DON'T rebuild the task, the Derived Column task won't pick up on the new data type. Once the task was recreated, I also no longer needed to cast the dates as strings before applying substring.

推荐答案

您在连接管理器中将列[Date_Column]设置为DT_STR.您指定的长度是多少?默认情况下为50.在派生列"中，其长度为10，这是错误消息由于潜在的数据丢失而无法转换".

You setup column [Date_Column] as DT_STR in Connection Manager. What is the length you specified? by default it is 50. In Derived Column, you used its length as 10, which is the error message "could not be converted because of a potential loss of data".

这篇关于在SSIS中从CSV导入yyyyMMdd日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！