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