Input parameter for SQL Execute Task

[flora9090]

Hi,
I use Execute SQL Task to insert my loading audit message into the table.
The sql is :
insert load_msg values( ?,getdate(),'load sucessfully')

I set input parameter to get data from one global variable which gets the value by reading content from one control file. (This global variable is string data type. The data always like '20050520' ) But by this way, I always get the error : " the invalid character for cast specification " .
but if I don't user input parameter, just hard code there like '20050520', then it works.

In table , this column is datetime data type.
I know that there is trick for the data type column. But how to solve this issue, why just entering the string data is ok but not ok using parameter?

Any idea would be appreciated

Flora

 

[mkal]

I tried to recreate your DTS package but I didn't have the control file so I just used a constant value of 20050220 that was mapped to the global variable before running the insert. And I recieved the same error message but once I changed the data from 20050220 to 2005-02-20 it ran with out error.

Hope this helps.

 

[flora9090]

Hi, mkal

That helps. Thanks
I tried to convert this global variable to the format as yyyy/mm/dd, then it can be taken and inserted into table.

In fact, the smiliar issue happened before.
so just give the idea here, in DTS, it's better to convert to date format as yyyy/mm/dd or yyyy-mm-dd if it is considered as date type and would be inserted into table, using format as yyyymmdd normally would cause error.

Flora