SSIS 中的数据转换 [英] Data Conversion in SSIS

问题描述

我昨天发布了一个与数据转换相关的问题.现在我将问题重新表述如下.

I posted one proble yesterday related to data conversion. Now i am rephraising the problem as below.

我有一个文本文件，其中我将日期和时间作为字符串.所以我需要阅读那个字符串(日期+时间)并将其转换为双

I have one Text fle where i have date and Time as string. So i need to read that strin (Date + Time) And onvert that in double

我们可以在下面的例子中看到.

We can see in the example below.

"Date"       "Time"       "Converted Double"
"01/31/2014" "30:00.0" to "41670.3541666667"

现在我已经完成了如下转换.

Now i have done conversion as below.

"日期 G" , "时间 G ", Concatinated_Date_Time"

"Date G " , "Time G ", Concatinated_Date_Time"

"41670.0000000000","00:00.0","01/31/2014 00:00.0",

"41670.0000000000","00:00.0", "01/31/2014 00:00.0",

我使用以下来转换日期 G(DT_NUMERIC,16,10)(DT_DATE)[日期 G ]

I used followin to convert Date G (DT_NUMERIC,16,10)(DT_DATE)[Date G ]

但是当我尝试对Concatinated_Date_Time"做同样的事情时，它给了我错误.我需要双重形式的Concatinated_Date_Time" (41670.548400000).

But when i try to do the same with "Concatinated_Date_Time" It gives me error. I need "Concatinated_Date_Time" in double form (41670.548400000).

请帮帮我.

谢谢

推荐答案

我终于想通了！

我按照以下步骤操作.

1. 将字符串DATE G"转换为 DT_DATE
2. DATEADD("Hh",0, 第 1 步的输出)

3. SUBSTRING("TIME G",1,2) 存储在一个输出中，SUBSTRING("TIME G",3,2) 存储在一个输出中，SUBSTRING("TIME G",5,3) 存储在一个输出中

1. Convert string "DATE G" in to DT_DATE
2. DATEADD("Hh",0, outputfrom step 1)

3. SUBSTRING("TIME G",1,2) store in one output, SUBSTRING("TIME G",3,2) store in one output, SUBSTRING("TIME G",5,3) store in one output

ADD all using DATEADD() 并将输出类型转换为 DT_DATE，因为它的输出是 DT_DBTIMESTAMP，不能直接转换为 DT_Numeric.

ADD all using DATEADD() and type cast the output to DT_DATE as its output is DT_DBTIMESTAMP which can not be converted to DT_Numeric directlly.

将类型转换为第 4 步的 (DT_NUMERIC) 输出

Type Cast to (DT_NUMERIC) output from step 4

谢谢大家

如果解释得不好，请告诉我.

Plese let me know if its not explained well.

谢谢大家

这篇关于SSIS 中的数据转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！