Hi!

So I talked to the DBAs and for some reason they just couldn't run the package on design mode to have a detail look on what data is going in and out in the package. To move away from that SQL script, I created a variable with an expression:

EXPRESSION1:

(DT_DATE)(SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8), 1,4) + "-" + SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8), 5,2) + "-" + RIGHT(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8),2))

That is to get the date on the filename and converting it to date data type. This is in turn being referenced by another variable to finally get the desired date based from the date above:

EXPRESSION2:

DAY(@[User::dFileDate]) >= 6 ? DATEADD("D", -DAY(@[User::dFileDate]), DATEADD( "MM", 1, @[User::dFileDate] ) ) : DATEADD( "DD", -DAY( @[User::dFileDate] ), @[User::dFileDate] )

They were tested in dev and works just fine but in prod, the EXPRESSION1 above fails with the error:

Description: Casting expression "(SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),1,4) + "-" + SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),5,2) + "-" + RIGHT(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),2))" from data type "DT_WSTR" to data type "DT_DATE" failed with error code 0xC00470C2. End Error Error: 2012-09-26 16:07:46.87 Code: 0xC0019004 Source: PackageName Description: The expression for variable "dFileDate" failed evaluation. There was an error in the expression. End Error

My dev and prod now have the same data being processed so I don't know what's causing the error this time knowing that they are both processing the same data. What's more weird is that in prod, the package seems to complete its run with all the data being loaded from that file to the table but fires the error above.

Am I missing something here?

I'm really thinking now that it could that language difference....