1

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, enter image description here

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 enter image description here

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

enter image description here

Castell James
  • 329
  • 6
  • 23
  • Possible duplicate of [How to pass variable as a parameter in Execute SQL Task SSIS?](https://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis) – J Weezy Jun 10 '18 at 22:03
  • 2
    Try to execute the SQL statement by replacing the ? with the actual values. It looks like it tries to cast your parameter value to match the [date] column. Or your parameter isn't defined properly and tries to insert a blank which, for obvious reasons, can't be cast to a date. In that case, see @J Weezy's comment. – ppijnenburg Jun 11 '18 at 08:44
  • Show us how you have the parameters mapped in your Data Source. – Tab Alleman Jun 11 '18 at 13:39
  • I update the post for more clarity. When I hard code the date parameter as 'yyyymmdd' it works. This can be seen up the updated post. – Castell James Jun 11 '18 at 14:07
  • Instead of using SQL command create a variable with your SQL and use Expression in the variable to build out the query with the dates in the expression then in the execute SQL use the Execute as Variable option instead. – Brad Jun 11 '18 at 14:45
  • @Brad is that this cannot be achieved using SQL command? – Castell James Jun 11 '18 at 15:12
  • It should be able to, but sometimes it is easier to debug if you populate the variable (and easier to identify issues) because you can see what the full query looks like in the expression builder. Also you can use a c# script task and a MessageBox in your code (or to some other way) to display the results of the variable on execution of the package debugging just before it executes (after the variables are populated). So you can see the exact SQL text a bit easier – Brad Jun 11 '18 at 15:15

1 Answers1

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).

enter image description here

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

enter image description here

Castell James
  • 329
  • 6
  • 23