I have a SSIS package the pulls data from a source table. This table has some "time" data types. When I execute that package in a standalone way, it's working perfectly.

When I chain it inside another SSIS package with the "Execute Package Task", it fails with weird conversions error telling me it can't convert those DB_TIME2 to DT_WSTR (a thing that is not done at all in the child package).

The child package contains a simple OLE DB Data source (select * from TABLE) and then insert into an exact copy of that table which was truncated just before this package, so I know the target table is empty.

Any idea why would it be different?

UPDATE: I tried to change all the "time" datatypes to "smalldatetime" (with 1900-01-01 for the date part) and my package now works in both mode (standalone and child). I changed it again to "time" instead of "smalldatetime", and it started to fail again. So in the end, I changed it back (again!) to "smalldatetime" and now it works. I'm starting to believe this is a bug in SSIS... I will build a demo of this and test it on various machines.

When you run it standalone, are you running it from the same environment that the first package is running from? It sounds like a version problem between the execution environments.
–
SqlACIDOct 25 '11 at 18:58

Yeah from the very same place, the same package, etc. I enabled warnings in the execution log, and it seems that when it gets executed as a child package, I get a warning message saying that the metadata is out of date for each of my "time" columns... very strange.
–
Dominic GouletOct 25 '11 at 19:10

Here's a thought. Is there any way that a column was added to your database table after you created the meta data from your select * statement (which is bad practice btw)? If a column was added to the table after your metadata was generated, you OLE DB data source metadata would be out of sync.
–
David BenhamOct 25 '11 at 19:32

No there is no way. And I know select * is bad practice, it was only a shorthand form to say I selected all my columns, but they are actually all written in the real query.
–
Dominic GouletOct 25 '11 at 19:47