SSIS Derived Column System Variable Length with ex

Question

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.

Answer 1

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.