将字符串转换为日期时间(使用 SSIS) [英] Convert String to Datetime (USING SSIS)

问题描述

我想将值5/27/2013 16:42:37.490000"(从平面文件(DT_STR)读取)插入 SQL Server 表的列(日期时间)中.如果我尝试在派生列中使用 (DT_DBDATE) 或 DT_DBTIMESTAMP 对其进行强制转换，则会出现错误.

I want to insert a value "5/27/2013 16:42:37.490000" (Read from a flat file (DT_STR)) into a column(datetime) of SQL Server table . If I try to cast it with (DT_DBDATE) or DT_DBTIMESTAMP in a derived column , it gives an error .

[Derived Column [130]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (130)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (155)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

我该怎么做?

谢谢

推荐答案

值为 datetime2 类型 .AFAIK SSIS 不支持 datetime2 .您需要将其作为字符串存储在数据库中，然后通过将列转换为 datetime2 来更新列.

The value is a datetime2 type .AFAIK SSIS doesn't support datetime2 .You need to store it in database as string and then update the column by converting it to datetime2.

这里是 Microsoft Connect问题

更新:使用DT_DBTIMESTAMP2，您可以将字符串转换为正确的日期时间格式

Update: Using DT_DBTIMESTAMP2 you can convert the string to proper datetime format

下面的代码在派生列中工作得很好

The below code works perfectly fine in Derived Column

(DT_DBTIMESTAMP2,7)"2013-5-27 16:42:37.490000"

7是这里的岁差.如果日期时间的格式不同，上面的代码将不起作用.例如MM/DD/YYYY HH:mm:ss.ffffff.

7 is the precession here .The above code won't work if the format of datetime is different.For example MM/DD/YYYY HH:mm:ss.ffffff .

但是您可以使用Script component 并将不同日期时间格式的数组传递给Datetime.ParseExact 函数

However you can use Script component and pass array of different datetime formats to Datetime.ParseExact function

Step1:拖拽一个Script组件，新建一个DT_DBTIMESTAMP数据类型的输出列，命名为NewDate.

Step1: Drag a Script component and create a new output column of DT_DBTIMESTAMP datatype and name it as NewDate.

Step2:编写下面的C#代码

Step2: Write the below C# code

public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
  string[] format = new string[] { @"M/dd/yyyy HH:mm:ss.ffffff",
                                         @"MM/dd/yyyy HH:mm:ss",
                                         @"M/d/yyyy HH:mm:ss" ,
                                         @"M/dd/yyyy HH:mm:ss.ffffff",
                                         @"MM/dd/yyyy HH:mm:ss.ffffff",
                                          @"M/d/yyyy HH:mm:ss.ffffff"};
  DateTime dt = DateTime.ParseExact(Row.Date,
                format ,
                CultureInfo.InvariantCulture,
                DateTimeStyles.None);

  Row.newDate = dt;
 }

这篇关于将字符串转换为日期时间(使用 SSIS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！