Tue 23 Jan 2007
I wanted to post about some different ways to work with varying data types in the derived column transformation. Some basic stuff follows:
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.
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.
March 12th, 2007 at 9:57 pm
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
March 12th, 2007 at 10:16 pm
There was a typo in the code; it has been fixed. The first image now works correctly. Thank you for letting me know.
May 16th, 2007 at 8:46 am
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
May 16th, 2007 at 12:28 pm
Suleyman,
Is the destination data type CHAR or VARCHAR?
May 16th, 2007 at 3:45 pm
Hi Phil
The destination column has datatype char(8).
Should it be varchar?
Thanks again.
May 16th, 2007 at 4:02 pm
Suleyman,
Yes. CHARs retain trailing spaces. VARCHARs do not.
May 18th, 2007 at 3:32 am
Hi Phil
Thanks a lot. It helped.
October 13th, 2008 at 4:45 am
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….
October 13th, 2008 at 9:40 am
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).
November 14th, 2008 at 1:55 pm
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
November 14th, 2008 at 1:58 pm
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”)
February 16th, 2009 at 3:05 am
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
July 27th, 2009 at 12:03 pm
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!
November 17th, 2009 at 10:18 am
Try to use the data conversion task rather than derive column
November 2nd, 2010 at 6:33 am
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.
April 23rd, 2011 at 3:01 am
Hey i just visited your site for the first time and i really liked it, i bookmarked it and will be back
May 3rd, 2011 at 1:43 pm
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
May 3rd, 2011 at 2:02 pm
Try: TRIM(LOWER(Col1)) == “bmp” ? col3 : col4 * 2
May 16th, 2011 at 4:42 am
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
July 10th, 2011 at 12:33 am
I just found the post through Yahoo. You really made a great point. Truly like to read more posts from your site.
July 21st, 2011 at 7:42 am
Awesome info once again! Thanks a lot.
August 15th, 2011 at 7:12 pm
I must say i thank you for publish. Great attitude!
August 23rd, 2011 at 9:41 am
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.
August 25th, 2011 at 6:41 am
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?
September 27th, 2011 at 9:10 am
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!
January 5th, 2012 at 1:30 am
I always was concerned in this subject and stock still am, thanks for posting .