I get the error,
Error converting data type varchar to date.
from this expression
(DT_STR,30,1252)@[User::ToDate]
DO NET Source [40]: An error occurred executing the provided SQL command: "EXEC [dbo.StoredProcedure] @ToDate = '01-maj-2018'
Error: 0xC004706B at Import (See Expression!), SSIS.Pipeline: "ADO NET Source" failed validation and returned validation status "VS_ISBROKEN".
@ToDate = '01-maj-2018'
How can I change it so my @ToDate will be 01-may-2018 not 01-maj-2018?
I tried to search and there is many similar question, but not a single question about this specific error. And why it even makes my string have maj?
I agree with @AlexanderVolok, it looks like the package has a LocalID different than English, or the Date time culture info selected in the Operating System Regional Settings are different.
If you can change these property then it may solves the issue, else you add an Expression Task before the Data Flow Task which convert the date to a string with different format using a conditional operator ? :, as example (Assuming that you are handling polish months names):
@[User::NewDataString] =
LEFT((DT_WSTR,50)@[User::ToDate],2) + "-" +
(SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "maj" ? "may" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "stycz" ? "Jan" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "lut" ? "feb" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "mar" ? "mar" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "maj" ? "may" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "czerw" ? "jun" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "lip" ? "jul" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "sierp" ? "aug" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "wrzes" ? "sep" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "pazdzier" ? "oct" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "listopad" ? "nov" : "dec" )
+ "-" + RIGHT((DT_WSTR,50)@[User::ToDate],4)
Then you should pass the new variable as parameter.
You can also use the an Expression Task, to convert months to numeric value such as:
@[User::NewDateString] =
RIGHT((DT_WSTR,50)@[User::ToDate],4) + "-" +
RIGHT("0" + (DT_WSTR,50)DATEPART("mm", @[User::ToDate]),2) + "-" +
LEFT((DT_WSTR,50)@[User::ToDate],2)
References