Search code examples
ssisderived-column

Creating derived columns from column of variable length components

I'm hoping to get your guidance on this. I have a column, consisting of variable length sections that needs to be split into separate additional columns. The column, PCMRetrievalCode is nvarchar type and consists of this format: 

i.e. PO607CON1324

PO = 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

So, in my SSIS package I'm trying to create these as derived columns, but am having trouble with the expression to split PCMRetrievalCode column, based on its variable length components for SchemaID and ContainerID. I've tried using the FINDSTRING function, since there's no CHARINDEX and am trying to use DT_WSTR, as this is the SSIS compatible datatype for the SQL Server nvarch datatype. These are the expressions I'm trying in the Transformation Editor:

enter image description here

I think the expressions for the PackageCode and SchemaID will hopefully be okay, but it's the ContainerID expression where I'm having difficulty. Any advice would be greatly appreciated. Thanks in advance!

Solution

  • Thanks for your suggestions, All!

    I was able to get it to work by using the following expressions 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)))