SSIS派生列表达式 [英] SSIS Derived Column Expression

问题描述

从平面文件中读取包含以下格式的年份和月份的列表 - >201212。

我需要将其插入到DATETIME2列，但首先我必须用/解析它，并添加一个01作为一天。这样201212将成为2012/12/01

我的派生列转换中的表达式如下所示：

<$ （DT_WSTR，4）SUBSTRING（RptMthDTM，1,4）+/+（DT_WSTR，2）SUBSTRING（RptMthDTM，5,2）+/ +01）

这似乎应该工作，SSIS接受这个解析表达式），但是当运行该包时，它会抛出一个完全无用的错误尝试执行类型转换时发生错误。连同列，它有错误。

我没有构建这个包，它被丢弃在我身上，我被告知要让它工作。 p>

提前感谢系列。

解决方案

DT_DBTIMESTAMP2只能从一个特定的字符串格式： yyyy-mm-dd hh：mm：ss [.fffffff]

这个表达式代替：

 （DT_DBTIMESTAMP2,0）（（DT_WSTR，4）SUBSTRING（RptMthDTM，1,4）+ - +（DT_WSTR，2）SUBSTRING（RptMthDTM，5,2）+ - +01 00:00:00）
  

更多细节：
[http://msdn.microsoft.com/en-us/library/ms141036.aspx] [1]

Reading from a flat file that has a column containing the year and month in the following format --> "201212".

I need to insert this into a DATETIME2 column but first I must parse it with "/" and add a "01" as the day. Such that 201212 would become 2012/12/01

My expression in my Derived Column Transformation looks like this:

(DT_DBTIMESTAMP2,0)((DT_WSTR,4)SUBSTRING(RptMthDTM,1,4) + "/" + (DT_WSTR,2)SUBSTRING(RptMthDTM,5,2) + "/" + "01")

This seems like it should work and SSIS accepts this(as in it can parse the expression) but when running the package it throws a completely useless error "An error occurred while attempting to perform a type cast." along with the column it had the error on.

I didn't build this package, it was pawned off on me and I was told to get it working.

Thanks in advance interwebs family.

解决方案

DT_DBTIMESTAMP2 can only be converted from a specific string format: yyyy-mm-dd hh:mm:ss[.fffffff]

You can use this expression instead:

(DT_DBTIMESTAMP2,0)((DT_WSTR,4)SUBSTRING(RptMthDTM,1,4) + "-" + (DT_WSTR,2)SUBSTRING(RptMthDTM,5,2) + "-" + "01 00:00:00")

More details: [http://msdn.microsoft.com/en-us/library/ms141036.aspx][1]

这篇关于SSIS派生列表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！