Changing Column size/type with Derived Column

I have a number of date columns that are parsed as DT_WSTR (6) and I have written a Derived Column converting them into DT_DATE via this (found on the forums) type expression:
(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))
But I really want to replace the current column, not create a new one. If I use "replace" the data is forced to be a DT_WSTR (6), and I get a truncation error at run-time.
Simeon
Simeon,
You're stuck with it I'm afraid. You can't change the type of a column in the derived column component. Its not a ahrdship to add it as a new column though, jsut don't use the existing one that's all!

-Jamie|||Could you change the source to return a larger column. This would solve your problem.|||Cheers Jamie,
That's what I thought, I was just hoping there was a way to keep it "clean".
On a related thought, I am finding that each time I alter any component near the top of a data flow, I end up needing to delete and re-add most the down stream components due to fields mismatching. Is this why people appear to be building there packages via code?
Simeon.
|||This does depend on the component, some just need double clicking on and the meta data should correct it self, others require you to select the mapped columns. The latter is generally when you change the names of components and inputs.

You shouldn't have to delete components though, I find that surprising.|||

SimonSa wrote:

Could you change the source to return a larger column. This would solve your problem.

That might work, but the derived column sets the type to DT_WSTR, so I'm not sure that putting a entry that is cast to DT_DATE would not upset it also.
|||

I was finding this while I was developing my source component. I had it wired to Raw Files (then later Trash Destinations) with Data Viewers to inspect the data. Running the package (after reloading BI) would give errors, so I found it easier to delete the source and it four outputs, and re-wire.
But going forward I'll try double clicking, and checking the mappings.

Simeon
|||Do you need to cast it to a date? If you do then you will have to have a new column, and the derived column is the best solution|||I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.|||

SimonSa wrote:

I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.

It was. I was slowly adding support for different data types, then adding support for foreign keys. The source component is like a flat file parser, but it handles files that have different rows (with different columns) that have relationships based on order. So really n tables with the foreign keys implied by what a row follows.
I was (for simplicity) developing support incrementally, with the relations setup by a function. The next step is to put that information into a configuration file.
|||A column is identified by it's lineage Id. Deleteing a column and adding it back, even with the same name and same data type properties will cause it to change lineage Id. This means downstream components that have referenced that column (by lineage Id) are now invalid. Opening the UI should bring up the mapping dialog, and one of the options is Map by Name. This normally solves most issues.

A well behaved component will not recreate the output buffer columns each time, but rather detect invalid columns, and remove, add new columns if required, and fix any columns in can detect on both sides, or leave alone matching columns. This can be a pain, as it is lots more code, but try the samples such as the ADO Source for some good template code.