带有表达式的 SSIS 派生列系统变量长度 [英] SSIS Derived Column System Variable Length with expression

问题描述

我有一个源列 KEY2，我需要在 SSIS 的派生列转换中对它执行一些字符串操作.配置如下

KEY2替换KEY2"(FINDSTRING(KEY,",",1) - 2) >0 ?SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : ""字符串 [DT_STR]11252(ANSI - 拉丁语 I)

长度是1，需要改成100，我试过这个:

(DT_STR,100)(FINDSTRING(KEY,",",1) - 2) >0 ?

SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : ""

但是，提示我解析错误.有什么问题?

更新

我遵循了 markTheLiars 的回答.表达式现在看起来像这样:

KEY2 替换 'KEY2' (DT_STR,100,1252)((FINDSTRING(KEY,",",1) - 2) > 0 ?SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : "") 字符串 [DT_STR] 1 1252(ANSI - 拉丁语 I)

表达式编译并运行，但是我被提示同样的错误，因为即使有强制转换，长度也不会改变.

解决方案

您的演员表缺少Code_page"参数:

(DT_STR, 100, 1252) ((FINDSTRING(KEY,",",1) - 2) > 0 ? SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : "")

1252 是默认值.请参阅

<小时>

最终发现，问题不在于这个组件，而是一个上一个组件，它在数据到达这个日期流组件之前截断了数据.在这种情况下，确定哪个组件导致截断的最简单方法是使用元数据查看器，可通过双击流路或右键单击并选择编辑"来获得:

这将快速为您提供有关变量及其源组件的信息.不幸的是，它不会告诉您截断发生的确切位置，但是如果您的数据流组件不是太复杂，您仍然应该能够相对较快地找到问题区域.

I have a source column, KEY2 that I need to perform some string manipulation on within the Derived Column transformation within SSIS. It's configured as the following

KEY2        
Replace 'KEY2'  

(FINDSTRING(KEY,",",1) - 2) > 0 ? 
  SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : ""

string [DT_STR]     
1                       
1252  (ANSI - Latin I)

The length is 1. I need to change it to 100. I have tried this:

(DT_STR,100)(FINDSTRING(KEY,",",1) - 2) > 0 ?

SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : ""

However, I am prompted with a parse error. What is the problem?

Update

I have followed markTheLiars' answer. The expression now looks like this:

KEY2        Replace 'KEY2'      (DT_STR,100,1252)((FINDSTRING(KEY,",",1) - 2) > 0 ? 
SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : "")     string [DT_STR]     1                       1252  (ANSI - Latin I)

The expression compiles and runs, however I am prompted with the same error as the length does not change even though there is a cast.

解决方案

Your cast is missing the "Code_page" parameter:

(DT_STR, 100, 1252) ((FINDSTRING(KEY,",",1) - 2) > 0 ? SUBSTRING(KEY,FINDSTRING(KEY,",",1) + 2,LEN(KEY) - FINDSTRING(KEY,",",1) - 1) : "")

1252 is the default value. See this answer for a much better explanation than I could give as to why it's important. See here for more info about casting/conversions.

---

It appears that the meta-data for that column is still set to be 1 character long. Right-click on the derived column transformation, select Show Advanced Editor, select the "Input and Output Properties". Expand "Derived Column Output" -> "Output Columns" -> Your column (Key2 in this instance I believe). Under "Data Type Properties" edit Length to be 100.

---

As was eventually discovered, the problem was not in this component but a previous component that was truncating the data before it ever reached this date flow component. In this case, the easiest way to determine which component is causing the truncation is to use the meta-data viewer, available by double-clicking on the flow pathway or right-clicking and selecting "Edit":

This will quickly give you info at a glance about the variables and their source components. Unfortunately it will not tell you exactly where the truncation occurred, but if your data flow component isn't too complicated you should still be able to find the problem area relatively quickly.

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