0

I am new to SSIS and I have searched to find the solution to this question. Any help is most appreciated!

I have a flat file with data defined as dt_wstr, to change the datatype I am using a data conversion to set the [column] to dt_str(50)

I am also using a derived column - to add as a new column: The goal is write an expression

I have a [column] which is defined as 11 characters

My question is how do I write an expression to only capture 10 characters, and anything greater than 10 I want to change the [column] to -1 else (dt_I8) [column]

I've tried:

FINDSTRING([Column],"9999999999",1) == 10 ? -1 : (DT_I8)TRIM([Column]) 

FINDSTRING([Column],"9999999999",1) > 10 ? -1 : (DT_I8)TRIM([Column]) 

LEN([Column]) == 10 ? -1 : (DT_I8)[column]

SUBSTRING( [Copy of Member ID] ,1,10)

The package runs without errors however the results in the table are not correct, the column with more than 10 characters are not showing up in the table

I am using visual studio 2012

Thank you Dawana

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
Dawana
  • 1
  • 1
  • Are you trying to write a [`CASE`](https://msdn.microsoft.com/en-us/library/ms181765.aspx) expression? – HABO Feb 02 '16 at 20:24

1 Answers1

0

I don't know why your substring attempt didn't work, but this would return the first 10 characters of column:

LEFT(column,10)

https://msdn.microsoft.com/en-us/library/hh231081(v=sql.110).aspx

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52