4

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
justromagod
  • 933
  • 9
  • 20
  • How are you passing value to @ToDate parameter? – Chetan Feb 20 '19 at 08:35
  • @[User::YYY] = EXEC dbo.XXX @ToDate = '"+ (DT_STR, 30,1252)@[User::ToDate] where @[User::ToDate] set as 1 of may – justromagod Feb 20 '19 at 08:43
  • Step to repro. 1.Set your Windows to be some local language. 2. Add DateTime variable ForCollationTest and set value to 1 may 2019 3. Add another variable (DT_WSTR, 30) ( @[User::ForCollationTest]), outcome will be in local language – justromagod Feb 20 '19 at 12:18
  • What data type is the date parameter in your stored procedure? If all variables are dates you should never have this problem and you should never have to worry about converting to specific formats. In other words, make sure the SP parameter is of type date and make sure the SSIS variable is also of type date. Locale should be irrelevant if you just use datatypes that don't care about format (i.e. dates not strings) – Nick.Mc Feb 20 '19 at 23:57
  • I generate SQL like Exec dbo.XXXX @x1 = '15 maj 2019' and this Exec fails – justromagod Feb 21 '19 at 13:34
  • Is `@x1` a `date` or `datetime` data type? When you do need to use a string literal (as in this case), use ISO format (yyyy-mm-dd) _or_ cast it with an explicit format – Nick.Mc Feb 24 '19 at 09:52
  • it is datetime. Casting to '2019-05-15' seems good workaround around SSIS maj issue – justromagod Feb 25 '19 at 10:01

3 Answers3

0

Don't know exactly about error but I assume it may be due to SQL Server Collation, try to check what is your server collation. Or may be exploring on collations may help you.

Collations

Mahesh
  • 198
  • 2
  • 16
0

It seems your package has LocaleID different than 1033 (English-US).

This value can be adjusted on a package level, so all underlying tasks will inherit it.

Can be also related to a Language setting of the SQL Server login if value is generated on SQL Server side

Related issue to check on what can affect date locale in SSIS: SSIS 2012 date formats dmy vs mdy

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
0

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.

(1) Changing months name using conditional ? :

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.

(2) Changing data format to yyyy-MM-dd

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

Hadi
  • 36,233
  • 13
  • 65
  • 124