Hello All, I am trying to upgrade the SSIS service from 2005 to 2008 R2. Unfortunately, I found the task"SQL task" I put in "event handler" part reporting the error as "Expression Truncation". In my tSQL task, it is pretty easy to insert one row into my SSISLog table. I override the SQLStatement in "expression" tab as below. Error 13 Error loading XXXX.dtsx: The expression ""INSERT INTO SSISLog (executeDate, eventType, packageName, taskName, sourceSystem, eventCode, eventDescription, packageDuration, containerDuration, insertCount, updateCount, deleteCount) VALUES ( '" + (DT_STR, 30, 1252)(DT_DBDATE) GETDATE() + " " + (DT_STR, 30, 1252)(DT_DBTIME) GETDATE() + "', 'OnPostExecute', '" + @[System::PackageName] + "', '" + @[System::SourceName] + "', '" + @[User::vDatabase] + "', "+ (DT_STR, 30, 1252) @[System::ErrorCode] + ", '" + REPLACE( @[System::ErrorDescription] , "'", "") + "', " + (DT_STR, 30, 1252) DATEDIFF( "ss", @[System::StartTime] , GETDATE() ) + ", "+ (DT_STR, 30, 1252) DATEDIFF( "ss", @[System::ContainerStartTime] , GETDATE() ) + ", " + "0" + ", " + "0" + ", " + "0" + ")"" on property "SqlStatementSource" cannot be evaluated. Error 14 Error loading SubscriberToLanding.dtsx: A truncation occurred during evaluation of the expression. Does anyone know why this occurs and how to solve this? Derek

Found the issue occurring in this part: (DT_STR, 30, 1252)(DT_DBTIME) GETDATE() .Derek

It works after changing " (DT_STR, 30, 1252)(DT_DBTIME) GETDATE() " to (DT_STR, 30, 1252)(DT_DBTIME2,3) GETDATE() . Why? Derek

And it only works the scale number is greater than 3. If changing to 2 like (DT_STR, 30, 1252)(DT_DBTIME2,2) GETDATE() , report the same error.Derek

It is happening because the source server is using a collation which has the DATETIME2 format, while your destination server has collation with DATETIME format. DATETIME has YYYY-MM-DD hh:mm:ss[.nnn] format and DATETIME2 follows YYYY-MM-DD hh:mm:ss[.nnnnnnn] format.http://thebipalace.wordpress.com

Is my understanding correct? If I change the source server from SQL2008 to SQL 2005, this feature should work even in SSIS 2008. Derek

It doesn't matter what is your source version. you must select the right "Collation" during the installation. The Collation must have the same date format on source and destination servers. If you are going to re-install your source, you can check what is the Collation set for destination server, and use the same on source server.http://thebipalace.wordpress.com

Yes, collation for all my servers is general_latin_CI_AS. Does collation decide the DATETIME2 format?Derek

Yes, collation for all my servers is general_latin_CI_AS. Does collation decide the DATETIME2 format? Derek The format is the same, no matter which collation you have (okay, the presentation of the date itself can change, but beneath the covers it remains the same). It is the fundamental difference between DATETIME and DATETIME2. The last one has a much bigger precision, while DATETIME only goes up to a precision of 3 milliseconds. SSIS is not really good at handling dates, as you've noticed by now. I usually cast the date to a string, do whatever I want with it, and at the end I cast it back to a date.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

What u said is definitely true. However I dont wanna modify anything in ETL packages upgrading. I hope SSIS 2008 can handle my packages in SQL2005. Even I have to change something but i wanna get a decent reason for that. For this case, I can not find the convincing reason why it does not work in SSIS2008. (It is easy to find the workarounds.)Derek

Try running SELECT * FROM sys.configurations WHERE configuration_id = 124 ORDER BY name ; on both of the servers, see what you get. http://thebipalace.wordpress.com

Hi Derek, Please refer to the simlar thread, and see if it work s for you. A truncation occurred during evaluation of the expression: http://www.bigresource.com/Tracker/Track-ms_sql-oYX3otrQ/ Hope it could help you. Thanks, Eileen