SSIS Derived Column System Variable Length with expression

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.

I think that cast is missing a parameter. Try (DT_STR,100, 1252) — Mike G
@mikeTheLiar, I have tried your suggestion. This time the error is: The data types DT_WSTR and DT_I4 are incompatible for binary operator ">". — w0051977
You're going to want to wrap the entire statement in a group then (it's performing the cast on the first element and then performing the comparison, when you want it the other way around — Mike G
@mikeTheLiar, I thought that was what I was doing. How do I cast the group? Thanks. — w0051977
If you look at the statement you're missing an ( before the first comparison. The (FINDSTRING(KEY,",",1) - 2) statement is being casted instead of the result. Just wrap the entire thing after the cast in (). This second issue just boils down to mis-matched parens — Mike G

Mike G Mike G · Accepted Answer · 2015-12-21T18:19:47

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 Derived Column System Variable Length with expression

1 Answers