Creating derived columns from column of variable length components
-
Hi All,
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. 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
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:
The data flow component is showing an error, "Error 40 Validation error. Extract VES Enrollment Data: Extract VES Enrollment Data: 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", but I thought that's what I was doing with the DT_WSTR conversion? Could someone please advise on what I'm doing wrong? Thanks in advance for your insight! - daniness - Thursday, April 19, 2018 3:08 PMHi All,
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. 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
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:
The data flow component is showing an error, "Error 40 Validation error. Extract VES Enrollment Data: Extract VES Enrollment Data: 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", but I thought that's what I was doing with the DT_WSTR conversion? Could someone please advise on what I'm doing wrong? Thanks in advance for your insight!Is the format always AANNNAAANNNN with the aforementioned information the numeric parts are variable? To put it another way, are the alpha parts consistent in the described lengths?
-
Hopefully this can get you started:
DECLARE @TestStr NVARCHAR(30) = N'PO607CON1324';
SELECT
LEFT(@TestStr,2)
, SUBSTRING(@TestStr,3, PATINDEX('%[0-9][A-Z]%',@TestStr) - 2)
, SUBSTRING(@TestStr,PATINDEX('%[0-9][A-Z]%',@TestStr) + 1, 3)
, RIGHT(@TestStr, PATINDEX('%[A-Z]%',REVERSE(@TestStr)) - 1)
- daniness - Thursday, April 19, 2018 3:08 PMHi All,
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. 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
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:
The data flow component is showing an error, "Error 40 Validation error. Extract VES Enrollment Data: Extract VES Enrollment Data: 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", but I thought that's what I was doing with the DT_WSTR conversion? Could someone please advise on what I'm doing wrong? Thanks in advance for your insight!See whether this helps:
It does make a couple of assumptions:- That the second character of your example, P0607CON1324, should have been an O rather than a zero.
- That CON is fixed text.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Lynn Pettis - Thursday, April 19, 2018 5:29 PMdaniness - Thursday, April 19, 2018 3:08 PMHi All,
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. 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
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:
The data flow component is showing an error, "Error 40 Validation error. Extract VES Enrollment Data: Extract VES Enrollment Data: 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", but I thought that's what I was doing with the DT_WSTR conversion? Could someone please advise on what I'm doing wrong? Thanks in advance for your insight!Is the format always AANNNAAANNNN with the aforementioned information the numeric parts are variable? To put it another way, are the alpha parts consistent in the described lengths?
Hi Lynn,
Yes, there'll always be 'PO' or 'PA' and a 'CON' in the lengths of 2 and 3, respectively.
- daniness - Friday, April 20, 2018 6:28 AM
Cool, then I believe my solution works in SSIS, whereas Lynn's uses T-SQL. Take your pick.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Phil Parkin - Friday, April 20, 2018 5:29 AMdaniness - Thursday, April 19, 2018 3:08 PMHi All,
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. 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
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:
The data flow component is showing an error, "Error 40 Validation error. Extract VES Enrollment Data: Extract VES Enrollment Data: 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", but I thought that's what I was doing with the DT_WSTR conversion? Could someone please advise on what I'm doing wrong? Thanks in advance for your insight!See whether this helps:
- That the second character of your example, P0607CON1324, should have been an O rather than a zero.
- That CON is fixed text.
Hi Phil,
Thanks for your suggestion. I think I seem to understand it, but a question for you. The StrippedRetrievalCode, would that be a User or System Variable I'd have to create?...or are you suggesting that it be another of the derived columns? Thanks! - daniness - Friday, April 20, 2018 8:54 AM
Excellent question.
I think that the easiest way for you to implement that as part of a derived column definition is just to inject the code directly, as follows.ContainerId = TOKEN(@[User::StrippedRetrievalCode], "CON", 2)
becomes
ContainerId = TOKEN(replace(REPLACE( @[User::PCMRetrievalCode] , "PO","" ),"PA",""), "CON", 2)If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Phil Parkin - Friday, April 20, 2018 9:16 AM
Hi Phil,
Thanks for your feedback. So I started to try implementing your suggestion, when I realized that I need to somehow assign the values from my table's rows for the column of PCMRetrievalCode to the user variable, User::PCMRetrievalCode. What would you advise would be the best or easiest way to do so? What I'm not understanding is how to go about ensuring that the PCMRetrievalCode variable gets the value from each row. As you can probably tell, I'm not an expert at all on this, so always need to read up...I've heard about ForEach containers, but not sure if this would serve the purpose? Could you please advise? Thank you! - daniness - Friday, April 20, 2018 11:59 AM
It's OK. I realise now that I could have been more helpful, I apologise 🙂
The difference between my suggested solution and what you are trying to do is that I did all of my work in the Variables pane on the Control Flow (which allowed me to create a suitable SSIS expression without having to go through the rigmarole of creating a dataflow).
However, when you are referring to column names from within a derived column transformation, all you need to do to reference the column is enclose it in square brackets.
So, to make my code work in your derived column expression, we get to this:
ContainerId = TOKEN(replace(REPLACE( [PCMRetrievalCode] , "PO","" ),"PA",""), "CON", 2)If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Phil Parkin - Friday, April 20, 2018 12:52 PM
Thanks, Phil. I just realized that I don't have the TOKEN function available :ermm::
Is there any other function I could use instead? - daniness - Friday, April 20, 2018 1:08 PMPhil Parkin - Friday, April 20, 2018 12:52 PM
Thanks, Phil. I just realized that I don't have the TOKEN function available :ermm::
Is there any other function I could use instead?Which version of SSIS are you using? 2008?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Phil,
I actually use BIDS:
-
OK, without TOKEN, this gets complicated, fast. Try this:
For SchemaId:
LEFT( replace(REPLACE( [PCMRetrievalCode] , "PO","" ),"PA","") , FINDSTRING( replace(REPLACE( [PCMRetrievalCode] , "PO","" ),"PA",""), "CON",1)-1 )And for ContainerId
RIGHT( [PCMRetrievalCode] , LEN ([PCMRetrievalCode])- FINDSTRING( [PCMRetrievalCode] , "CON",1)-2)If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Hi 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. 🙂
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply