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

Posted on 2011-10-21
Last Modified: 2012-05-12
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

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
Question by:blossompark

    Author Comment

    am using this for the CN derived column expression
    SUBSTRING(NAME,4,13) works fine
    giving INBAPARAG0001
     but i'm not sure if the CN portion will always be 13 characters in lenght

    Author Comment

    I am currently using the following for the CN derived column expression

    (SUBSTRING((SUBSTRING([ Name],FINDSTRING([ Name],"CN",1),150)),4,150)),

    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

    any ideas as to what is wrong with my REPLACE expression?
    LVL 16

    Accepted Solution

    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
    LVL 37

    Assisted Solution

    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.

    Author Comment

    Hi vdr1620 and ValentinoV,
    thanks for your responses...
    vdr1620...will try your code and update later...

    Author Closing Comment

    Hi perfectly....Many Thanks!!

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

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now