Derived Column

I wanted to post about some different ways to work with varying data types in the derived column transformation. Some basic stuff follows:

Derived Column

Notice that there are three different examples of how to work with the derived column… The first example uses a DT_WSTR column and outputs a NEW column with a data type of DT_WSTR. The second example does exactly the same thing, except the output data type for a second, new column is DT_I4 (integer). The third example actually replaces an integer column, ClientNumber with the values of the conditional expression.

Derived Column Date Example

If you are working on date fields, DT_STR input types can be implicitly cast to date data types. An example follows. The input column, [column10] is of type DT_STR.

35 thoughts on “Derived Column

  • khanhmy

    Hi Phil,

    Your advices are very helpful. Thank you for sharing us your experiences.

    To make your explaination clearer, please zoom the the first form bigger.

    Would you please show me more expression-source used in derived column.

    Thanks alot,

    khanhmy

  • Phil Brammer

    There was a typo in the code; it has been fixed. The first image now works correctly. Thank you for letting me know.

  • Süleyman

    Hi,

    I have a problem while loading a fixed width flat file and saving it to a OLE DB Destination after some transformation with the derived column shape.

    The transformation Trims a column for leading and trailing spaces.

    However, when the column is saved to the database, the spaces are still there.

    I think it is because of the output column datatype and -length of the transformation shape.

    If I trim a string of length 8 (X) and get a string of length 6 (Y) and save it in an output column of length 8 after which I save it in the DB Y occures with 2 trailing spaces in the DB. I wanted to save X as Y in the DB.

    Can any one help me with this problem.

    Thanks on advance

  • Phil Brammer

    Suleyman,
    Is the destination data type CHAR or VARCHAR?

  • Süleyman

    Hi Phil

    The destination column has datatype char(8).
    Should it be varchar?

    Thanks again.

  • Phil Brammer

    Suleyman,
    Yes. CHARs retain trailing spaces. VARCHARs do not.

  • Süleyman

    Hi Phil

    Thanks a lot. It helped. 🙂

  • kote_alex

    Hello … I’m a newbie and I’ved runed into a problem … I have a text file exported from AS-400 and I need to convert it to a table. A particular colum is requierd to be decima 8,4 and the numbers in flat file are like this 26981500 and visual studio converts it to 269812.0000 … when it should have done this 2698.1200 …. The setings in the flat file connection manager are set for that colum decimal 8 and 4 … and in the sql table are decimal 10 and 4 ….. because if i set it decimal 8 4 then I get an error message reguarding Overflow component….

  • Phil Brammer

    Your source needs to define that column as an integer. Then, in your derived column, you’ll need to divide by 10000.00, and cast the result to DECIMAL(8,4).

  • Ting

    Hi Phil,

    Your article has been most helpful. However, i ran into problem when trying to add several numeric type coulmns together.

    Instead of giving me the sum of the columns, it produced NULL value.

    The output column from excel source is float.

    Any suggestion is really appreciated.

    Thanks,
    Ting

  • Phil Brammer

    Ting,
    Can any of the columns be NULL? If so, make sure you test each column for NULL and if it is, set the result to zero, otherwise return the value of the column. If one column is null, it will cause problems. (That is, you can’t add a NULL value to a known value as the result will be “unknown”)

  • Elie

    Hi, please can someone help me i have the following problem:
    i am running a package and at one point (in a dataflow), it seems like it freezes and the components are all yellow in the same dataflow.when i remove the derived columns from the dataflow it works properly. it shows 502 records and stop.when removing the derived column it shows 5060 records

  • Alison

    Hi,
    I am working with a flat file (mainframe) importing into a OLE DB destination and I have a value of 00001212670 coming in and when I try to load as decimal it puts leading zeros on the end.

    Instead of 12126.70 it is 1212670.00

    Help!!

    I tried the derived column like above with dividing by zero but all the outputs turn into zeros.

    Thanks in advance for your help!

  • Doyle

    Try to use the data conversion task rather than derive column

  • hassan

    i have converted many text,acess,acess,oracle data to sql now i have to perform data cleansing.

    i ,m new to SSIS and i don`t know how to use SSIS to perform my data cleansing before ETL. i have used some queries in my SQL to check dirty data,

    for gender i found 8 values with (00,11,01,10,001,M) .

    similarly, for date of birth
    12 invalid dates. 22-Jal-75,1/27/75,27-Apl-77….. 29-Feb-75,31-jun-89 etc.

    WHAT I WANT????

    1)first SSIS read data.

    2)if don`t find 0 or 1 in gender send erronous data(00,11,01,10,001,M) to “error table”.

    3)in “error table”, dirty data (00,11,01,10,001,M) should be converted in 0,1

    4)after standarization, error table data records should go to original table at its place.

    5)Now i will change 0 to male and 1 to female.

    6)now i will send complete table of student(stid,name,father,gender,adress,last degree,reg data,dob) to a new table STD_INFO.

    7) i will repeat same 1-6 steps for all databases(lahore,karachi,islamabad,peshawar) and then i will integrate STD_INFO tables of all databases into one.

    i hope now you can understand what i have to do. Similar case with DOB first dirty data to erronous table after conversion there to original table and then i will check all records at the same time.

  • John Cotsmur

    Hey i just visited your site for the first time and i really liked it, i bookmarked it and will be back 😀

  • siham

    bonjour

    l’expression que j ai mise dans la colonne dérivée est suivante

    Col1 == “bmp” ? col3 : col4*2
    le problème est que la valeur de la colonne col1 est toujours considéré différente de bmp pourtant bmp existe dans col1.Et donc dans le résultat me renvoie toujours col4 *B2 même si col1 == bmp

    • Phil Brammer

      Try: TRIM(LOWER(Col1)) == “bmp” ? col3 : col4 * 2

  • eenzijdige backlinks

    An interesting discussion is worth comment. I think that it’s best to write extra on this topic, it might not be a taboo topic but usually individuals are not sufficient to speak on such topics. To the next. Cheers

  • Jeff Peedin

    I just found the post through Yahoo. You really made a great point. Truly like to read more posts from your site.

  • Jack Nicholson

    Awesome info once again! Thanks a lot.

  • Ignacio Lugo

    I must say i thank you for publish. Great attitude!

  • Libby

    In SSIS, I’m trying to import a flat file to a table. A couple of the fields on the table are defined as a datatype of dec(9,2). I’m having the same issue as someone else mentioned above. I need 12345 to result in 123.45 – but I’m getting 12345.00. Your reponse was helpful “Your source needs to define that column as an integer. Then, in your derived column, you’ll need to divide by 10000.00, and cast the result to DECIMAL(8,4).”, but could you please elaborate and tell me what the format of the expression should be for the derived column? Thanks a bunch.

  • Prestashop modules

    What an excellent text! No idea how you wrote this post..it’d take me long hours. Well worth it though, I’d assume. Have you considered selling banners on your blog?

  • SMITH

    Hi,

    How do you copy an expression from notepad into a derived column editor in SSIS ? I’m trying it but it is not copying . Please help!

  • free mobile apps

    I always was concerned in this subject and stock still am, thanks for posting .

  • Vinod

    Hi,

    We are trying to create dtsx on the fly with derived columns. We are trying to read a text fle and depending on some fixed position transforming to a table in oledb using substring. In the expression we use the SUBSTRING function and the column reference is given as lineage id. However once the output of dtsx is opened the dataflow shows as error. I have to manually go to derived column click on the derived column expression and come back and then only the error disappears. Can you help me find a solution

  • Vinod

    Solution is as below:
    For the derived column set I need to give reference of Source’s Input reference.
    IDTSInputColumn100 inputCol;[0].InputColumnCollection.New();
    IDTSVirtualInput100 vInput = derivedCol.InputCollection[0].GetVirtualInput();
    inputCol = instanceSource.SetUsageType(derivedCol.InputCollection[0].ID, vInput, col.LineageID, DTSUsageType.UT_READONLY);

  • Hayden Sramek

    I am often to running a blog and i really appreciate your content. The article has really peaks my interest. I am going to bookmark your website and maintain checking for new information.

  • Rolanda Anspach

    Hello I am so thrilled I found your blog page, I really found you by accident, while I was browsing on Aol for something else, Anyways I am here now and would just like to say thank you for a remarkable post and a all round interesting blog (I also love the theme/design), I don’t have time to read through it all at the moment but I have saved it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the great job.

  • Click This Link

    I needed in order to really write people that very little note just to give thanks once again on the great pointers you have discussed on this site. It’s simply seriously generous of people like you to supply unreservedly precisely what most of us could possibly have offered for sale as an ebook to make some money on their own, certainly considering that individuals could possibly have tried it in the event you considered necessary. The ideas also worked to become good way to fully grasp other people online have similar fervor just as my own to see whole lot more in respect of this condition. I know there are a lot more pleasant opportunities ahead for those who examine your blog.

  • Reece

    The attacker is then marked with the antibody so that
    the body knows it is threatening and it can be killed.

  • Magnetic Messaging Ebook Free Download

    Managing the technique’s sources is a single of its responsibilities.

  • socrates theme review

    Or a home (like most other Forms are regarded as)?

Comments are closed.