Creating derived columns from column of variable length components
- daniness - Monday, April 23, 2018 9:37 AMHi Phil,
So, it turns out that the version of BIDS I'm using doesn't have the LEFT function either, but I was able to rewrite the SchemaID expression as:
SUBSTRING(((DT_WSTR, 3)[PCMRetrievalCode]),3,(FINDSTRING([PCMRetrievalCode] ,"C",1))-1) , but I'm still trying to workout the correct syntax for the ContainerID expression, as it's not seeming to like:RIGHT((DT_WSTR, 3)([PCMRetrievalCode],LEN([PCMRetrievalCode])-FINDSTRING( (DT_WSTR,3([PCMRetrievalCode]),"CON",1)-2)) ...still trying to work this out...
Thanks for all of your suggestions.
My suggested expression does not contain the (DT_WSTR, 3) stuff. Why do you need that?
-
If I don't use (or attempt to use) the (DT_WSTR,3), then it gives me a 'The function "FINDSTRING" does not support the data type "DT_NTEXT" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.' error...I'm thinking this hopefully will correct this error?
- daniness - Monday, April 23, 2018 11:05 AM
Please don't forget that the second argument of (DT_WSTR,3) denotes the length of the returned string. 3 is not long enough for your purposes.
-
Thanks, Phil. I'm thinking I should use 5 for as the 2nd argument of (DT_WSTR), as the container ID could potentially be this length, however, SSIS doesn't seem to be liking the expression I'm trying to use:
RIGHT(((DT_WSTR,5)PCMRetrievalCode),(LEN(PCMRetrievalCode) – FINDSTRING(((DT_WSTR,5)PCMRetrievalCode),"CON",1) - 2)) ...it's giving me the error, "The token"[]" at line number "1", character number"60" was not recognized. The expression cannot be parsed because itcontains invalid elements at the location specified."
Any idea what I'm doing wrong? Thanks in advance. -
Okay, so this is my latest attempt to try coding the SSIS expression for the original scenario of the PCMRetrievalCode column, particularly the ContainerID portion:
i.e. P0607CON1324PO = Non-Package or PA = Package
607 = SchemaId --> could be more than 3 numbers, as the schema increases
CON = Container
1324 = ContainerId --> could be 3-5 numbers
SUBSTRING((DT_WSTR,5)PCMRetrievalCode, FINDSTRING((DT_WSTR,1)PCMRetrievalCode, "N", 1)+1, LEN( DT_WSTR,20)PCMRetrievalCode - (FINDSTRING((DT_WSTR,1)PCMRetrievalCode,"N", 1)+1)
...but SSIS is still not liking it, as it's returning errors mostly saying it's invalid. :unsure:
...any suggestions would be greatly appreciated...many thanks in advance! -
In case you have not solved this yet, please try the following:
RIGHT( (DT_WSTR, 15) [PCMRetrievalCode] , LEN ( (DT_WSTR, 15) [PCMRetrievalCode] )- FINDSTRING( (DT_WSTR, 15)[PCMRetrievalCode] , "CON",1)-2) -
Thanks for all of your input, @Phil Parkin and everyone! I was able to finally get this to work by using the following for the derived columns:
PackageCode: SUBSTRING(((DT_WSTR,15)PCMRetrievalCode),1,2)
SchemaID: SUBSTRING((DT_WSTR,15)PCMRetrievalCode,3,FINDSTRING(((DT_WSTR,15)
PCMRetrievalCode),"C",1) - 3)ContainerID: RIGHT(((DT_WSTR,15)PCMRetrievalCode),(LEN((DT_WSTR,15)PCMRetrievalCode) - FINDSTRING((DT_WSTR,15)PCMRetrievalCode,"N",1)))
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply