Pass Parameters to OLE DB SOURCE

Question

I am new to SSIS and having trouble passing parameters to OLE DB Source. I want to query data for a given date range.

I have two variables, variable1 of data type Date Time and the value of the variable is set by expression DATEADD("day", -1, GETDATE()). variable2 of data type Date Time and the value of the variable is set by expression DATEADD("day", 0, GETDATE())

Inside the OLE DB SOURCE the data access mode is set to SQL command, below is the SQL Code.

Select Col1, col2, col3, col4, coldate where Col1 = 'abc' and  coldate between convert(varchar(10), ?, 101) and convert(varchar(10), ?, 101)

I have mapped the parameters as

Parameter0, User::variable1, input

Parameter1, User::variable2, input

When I hit preview, I get an error

"There was an error displaying the preview

Additional Information: No Value given for one or more required parameters. (Microsoft SQL Server Native Client 11.0)"

When I debug the task the error I get is

[OLE DB Source [38]] 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".

Note: The datatype for column coldate is datetime

Please help me resolve this issue. I was able to query successfully using data access mode SQL command from variable inside OLE DB Source but I was asked not to use data access mode as SQL command. Thank you.

Answer 1

If you are sure you are mapping the variables right then this is what I would suggest:

SSIS uses it's own data types. I've had trouble in the past working with SSIS datetime so whenever I pass variables to TSQL statements from within SSIS I try to work with strings only (if at all possible).

I recommend setting up your variables in SSIS as strings that receive the date values you are trying to filter formatted as such. You could do the conversion to string in SSIS within the same statement you are currently using to implement your dateadd statements.

SSIS Variable Expression Example:

(DT_STR,4,1252) DatePart("yyyy",dateadd("day", -1, getdate())) + "-" +
Right("0" + (DT_STR,4,1252) DatePart("m", dateadd("day", -1, getdate())),2) + "-" +
Right("0" + (DT_STR,4,1252) DatePart("d", dateadd("day", -1, getdate())),2)  

Then simplify your TSQL statement like this:

Select Col1, col2, col3, col4, coldate 
where Col1 = 'abc' 
and coldate between ? and ?

Side note. When you pass your TSQL statement in this way. SQL Server knows that when using a date column and a between statement the boundaries passed as strings should be converted to the sql server column type datetime. The between will still make use of the proper indexes if available. It doesn't fall prey to the implicit conversions that might thwart proper index selection in other queries that receive improper types for comparison. You can test this in SSMS via show execution plan.