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”)