Derived column expression only capture 10 characters

Question

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

Answer 1

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