1

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.

SteveC
  • 15,808
  • 23
  • 102
  • 173
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • I think that cast is missing a parameter. Try `(DT_STR,100, 1252)` – Mike G Dec 21 '15 at 18:11
  • @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 Dec 21 '15 at 18:14
  • 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 Dec 21 '15 at 18:15
  • @mikeTheLiar, I thought that was what I was doing. How do I cast the group? Thanks. – w0051977 Dec 21 '15 at 18:20
  • 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 Dec 21 '15 at 18:24
  • A cast does not seem to make a difference. Please see the update to my question. – w0051977 Dec 21 '15 at 19:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98604/discussion-between-miketheliar-and-w0051977). – Mike G Dec 21 '15 at 20:14

1 Answers1

6

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.

SSIS Advanced Editor

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":

flow meta-data

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.

Community
  • 1
  • 1
Mike G
  • 4,232
  • 9
  • 40
  • 66
  • There is no output column. I try to add one called 'KEY2' and I am prompted with a compile error: all derived columns are required to have unique names. – w0051977 Dec 21 '15 at 20:25
  • I resolved this quickly by changing the length of the output column of the source component: Key2. Your answer was almost correct. It was the best answer. – w0051977 Dec 29 '15 at 15:53
  • Ahhh, yes. That makes sense. If a previous component was truncating the input than it would have to be fixed there. If you didn't already know, you can right-click on pathways between components and view the meta-data - that's been helpful for me several times in the past. – Mike G Dec 29 '15 at 15:55
  • I did not know you can view the metadata by right clicking on the pathways. Could you amend the question with that information for future reference? +1. – w0051977 Dec 29 '15 at 15:57
  • 1
    Done. Unfortunately SSIS can be quite inscrutable at times. When it works it's great - when it doesn't work it can make you want to tear your hair out. – Mike G Dec 29 '15 at 16:11