Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rcdeeley
Starting Member
3 Posts |
 Posted - 2011-09-30 : 11:26:51
|
| I am attempting to create derived columns from a single column. The data elements I am trying to extract are separated by hyphens in a column named record_id.Example: '7-1-201035461-12'I am using this expression to derive the first column:SUBSTRING(record_id,1,FINDSTRING("-",record_id,1) - 1)Which I would expect to give a value of '7', however I get this error:[Derived Column [23]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (23)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "source_id" (1057)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. This seems to be a straight forward derivation, can anybody offer a suggestion as to how I am doing this wrong?Thanks, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-30 : 11:30:50
|
| is the format consistent? are there values without '-' s? in any case, try below tooSUBSTRING(record_id+'-',1,FINDSTRING("-",record_id+'-',1) - 1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-09-30 : 11:36:08
|
| In the FINDSTRING function you have the first two parameters - character expression and search string - reversed. Change it to FINDSTRING(record_id,"-",1) http://msdn.microsoft.com/en-us/library/ms141748.aspx |
|
|
|
rcdeeley
Starting Member
3 Posts |
Posted - 2011-09-30 : 12:31:46
|
| Thanks for the help, I did have the parameters reversed. Works as expected now. |
|
|
|
|
|
|
|
|
Using Substring and Findstring in Derived Column