[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

using Derived Column Transformation Editor in sql server 2005 SSIS package creation

I have a column in  a source called Name, that typically contains the following data;
CN=INBAPARAG0001, OU=Paris, OU=Remote, OU=Clients,OU=Partners,DC=americas,DC=acme,DC=net

Using the derived column transformation editor in SSIS, I want to create the following derived columns
CN                           OU                                                 DC
INBAPARAG0001      Partners/Clients/Remote/Paris      americas.acme.net


For the CN column I am thinking I should use a combination of the SUBSTRING Function and the LTRIM Function in the expression  but I cannot guarantee that the CN value will be a consistent length,
Is there someway to tell the function that I want “everything returned as far as the first comma”?
Any guidance appreciated, thanks
 derived_columns
0
blossompark
Asked:
blossompark
  • 4
2 Solutions
 
blossomparkAuthor Commented:
Hi,
am using this for the CN derived column expression
SUBSTRING(NAME,4,13)
...it works fine
giving INBAPARAG0001
 but i'm not sure if the CN portion will always be 13 characters in lenght
0
 
blossomparkAuthor Commented:
I am currently using the following for the CN derived column expression
;

REPLACE(
(SUBSTRING((SUBSTRING([ Name],FINDSTRING([ Name],"CN",1),150)),4,150)),
(DT_STR,30,1252)FINDSTRING(SUBSTRING((SUBSTRING([Name],FINDSTRING([Name],"CN",1),150)),4,150),",",1),
""
)


For a typical input of
CN=INBAPARAG0001, OU=Paris, OU=Remote, OU=Clients,OU=Partners,DC=americas,DC=acme,DC=net

it returns
INBAPARAG0001, OU=Paris, OU=Remote, OU=Clients,OU=Partners,DC=americas,DC=acme,DC=net

however i expect it to return
INBAPARAG0001

any ideas as to what is wrong with my REPLACE expression?
0
 
vdr1620Commented:
You don't need a Replace function to derive CN Value.. try the below expressions

For CN -
SUBSTRING( [Name], FINDSTRING( [Name],"CN=",1)+3, FINDSTRING([Name],",",1) -  FINDSTRING([Name],"CN=",1)-3)

For OU -
REPLACE(REPLACE(SUBSTRING(  [Name], FINDSTRING( [Name],"OU=",1)+3, FINDSTRING( [Name],",",5) -  FINDSTRING( [Name],"OU=",1)-3),"OU=",""), ",","/")

For DC -
REPLACE(SUBSTRING( [Name], FINDSTRING([Name],"DC=",1)+3, FINDSTRING([Name],",",5) -  FINDSTRING([Name],"OU=",1)-3),",DC=",".")


Let me know.. if they don't work
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
ValentinoVBI ConsultantCommented:
I think for this type of data manipulation, you're better off using the Script Component.  Using C# or VB.NET, you could split the string on comma, then process each part separately and put it in its respective data flow column.

It would be more readable in case you (or your colleage) needs to modify it afterwards.
0
 
blossomparkAuthor Commented:
Hi vdr1620 and ValentinoV,
thanks for your responses...
vdr1620...will try your code and update later...
0
 
blossomparkAuthor Commented:
Hi vdr1620:....works perfectly....Many Thanks!!

Hi ValentinoV...thanks for your comment, I havent acted on it, but is useful for me to consider in future...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now