October 5, 2011 at 4:31 pm
Hello. It's been a while since I did any work with SSIS packages, and I'm having problems it looks like with my query parameters. In my Data Flow tab, I have an OLE DB Source going to a Data Conversion that then points to Excel Destination. When I hard-code data for where the parameters are in my query, it works as expected. However when I try to put the parameters in, I get the following error message:
[OLE DB Source [1]] 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 10.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
My query is as follows when hard-coded:
SELECT TOP (10) device_psg_id, load_w, metric_dt, pos, command_id, hour_start_utc, hour_end_utc, is_online, load_type, task_id, context_id
FROM report_proc.integrable_load_metric
WHERE DATEPART(HOUR, hour_end_utc) = 20
and task_id = 'CBE910C9-37A9-4985-B143-43E91A150089'
and context_id ='6B204D61-ABD2-4FDA-BF1E-4336A8EC894C'
It fails when I change it to the following and click on the Parameters tab and create new Variables (see after query):
SELECT TOP (10) device_psg_id, load_w, metric_dt, pos, command_id, hour_start_utc, hour_end_utc, is_online, load_type, task_id, context_id
FROM report_proc.integrable_load_metric
WHERE DATEPART(HOUR, hour_end_utc) = 20
and task_id = ?
and context_id =?
Parameter Mappings:
@Event -> User::Event
@Context_ID -> User::Context_ID
Does anyone have a suggestion?
October 5, 2011 at 4:40 pm
Check the dataypes of the parameters and what the data types of the variables are; to make sure that they are consitent with the column data types in the where clause.
October 6, 2011 at 8:57 am
Hi SSCrazy - thanks for response!
I checked and both variables are set to String. Not sure how to check Parameters in BIDS? I know in the query one of the fields is varchar and the other is uniqueidentifier? Is the uniqueidentifier the problem? Should I convert it to varchar?
Thanks again!
October 6, 2011 at 9:22 am
sorry i was thinking of the datatypes in the execute SQL tasks.
yes I think you convert the unique ID to varchar for the query.
October 6, 2011 at 9:34 am
Ok, I tried converting to varchar the field that was uniqueidentifier. However I still received the following error message:
[OLE DB Source [1]] 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 10.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
October 6, 2011 at 10:00 am
is there any chance you can run a trace against SQL server to see what SSIS is throwing at it?
if so then you should be able to work out where the conversion error is;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply