I'm trying to author a .dts package that will combine two number fields (in
my source MS Access database) to form a date, which would then be stored as a
DateTime in my destination SQL Server 2005.

I'm using the following expression to create the derived colum:

[Day] + "-" + [Month] + "-2005"

Where Day and Month are number columns in my source MS Access Database. I'm
expecting to get a result like: 31-Dec-2005.

The expression I'm using to create this derived field keeps throwing an
"invalid cast exception". Specifically it says, "The datatypes DT_I4 and
DT_WSTR are incompatible with the binary operator +" etc..

How do I cast the [Day] and [Month] fields properly to combine them as a SQL
DateTime?

Your derived column would probably want to look like this

(DT_WSTR,2)DayColumn + "-" + (DT_WSTR,2)MonthColumn + "-2005"


HTH

Allan



"rswolff (AT) noemail (DOT) noemail"
<rswolffnoemailnoemail (AT) discussions (DOT) microsoft.com> wrote

Hi,

I assume you mean a .dtsx package (SSIS).

Try casting the numeric values which are, presumably, DT_I4 to
DT_WSTR before concatenating. Assuming you are allowed to cast that,
then you are simply concatenating string values, which ought to be ok.

Andrew Watt
MVP - InfoPath

On Mon, 10 Oct 2005 13:37:03 -0700, "rswolff (AT) noemail (DOT) noemail"
<rswolffnoemailnoemail (AT) discussions (DOT) microsoft.com> wrote: