SSIS Error Code DTS_E_OLEDBERROR 0x80040E21 Invalid character value for cast specification

Question

I am new to SSIS and just started experimenting. I am trying to figure out how to pass parameters from the ssis project to sql statement in the project. I have an sql script defined in an OLE DB data source with the access mode to the data source being an SQL command. In the where clause, I have where date between ? and ? and create two project level parameters, startdate and enddate,

which i want to be read into the where clause at run-time. When I run the package I get this error

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

Not sure where I am going wrong. Can someone please assist.

I am NOT using execute SQL Task, I have my script in ole db source connector

And this is how I have my variable for the ? parameters

Answer 1

The was partly due to casting issue with the date. In SSIS, in seems dates in the format yyyymmdd don't automatically convert to a date datatype like it does in t-sql as such it throws a "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

To fix this, I create two variables startdate and enddate and used expressions to convert my project level parameters $Project::startdate and $Project::enddate which were in yyyymmdd format to a date datatype like so SUBSTRING(@[$Project::startdate],1,4) + "-" + SUBSTRING(@[$Project::startdate],5,2) + "-" + SUBSTRING(@[$Project::startdate],7,2).

Then mapped by new variables to the parameters in my SQL script like so: