Invalid Character value for cast specification

[HBWAL]

In my DTS Package I have a step that Executes a sql statement: Insert into table values (5, ?, ?). The parameters are set to globals. The first global is a date and the second a text. Upon execution I get the error indicated in the title. I have ran the same statement from analyzer inserting the values of the globals and no problem. I think it has something to do with my date value. I'm new to Sql and DTS and it's the little things like this that makes me want to bang my head. Thanks in advance for any help you can provide.

 

[JayKusch]

Please post your code for the INSERT statement as well as an example of the date string you are trying to enter.

Thanks

J. Kusch

 

[MDXer]

You can use SQL Profiler to capture the exact statement being executed. Sometimes what gets executed is not what you belived it was.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[HBWAL]

my statement "Insert Into dbo.tListLogFiles
values (5,?,?)"
param1 value = "200407060953OpenPTR.csv" (string variable to varchar)
param2value = 7/6/2004 9:53:00 AM (date variable to datetime datatype

My current status: 2 problems The date will insert but only the date, not the time. The text will not insert at all. I've done some searching but this error is usually attributed with a smalldate type mismatch. This is not my problem. Thanks for your help.

 

[JayKusch]

Is the length if the input variable for the DateTime string large enough or is it truncating the time?

Thanks

J. Kusch

 

[HBWAL]

It is a global variable and I selected date from the datatype dropdown. The value shown from the same form is the whole date. Do I have another option? The most confusing thing is why it won't accept my text. I finally added 2 fields to my import table. In the transformation I assign them the global variables. Works like a charm. I just can't make the parameters work outside of a select statement.

 

[DanEvans]

I know this is a little late, but I could see that noone had answered this, so I thought I would give my two bits for posterity.

I was having the same problem, and what I discovered is that DTS was throwing the "Invalid character..." error whenever I tried to cast or convert in an Execute SQL Task. The way I got around this was by setting the DTS global variabal as a string data type, and assigning it its value in a dynamic properties with a query that cast the date I needed as a string friendly to implicit date conversions.

This way, in my Execute SQL Task, I could put a query like:
Insert into tableA
select * from tableB
where mydate >= ?
and it would work be implicity converting my date string.

Hope this helps someone...worked for me.

Thanks,
Dan