Invalidcharacter value for cast specification

[Former Member]

Hello All,

We are migrating our legacy data from Oracle to SQL SERVER 2016.

SAP BODS SP4 and SQL SERVER 2016 are softare version.

we are pulling data from oracle through ODBC connection and loading. There are few column in SQL SERVER 2016 (Target) with datatype as datetime.

So i need to populate the start date and end-time in those target colums. I am passing values to this colum through global variables. But i am getting the error..

1482014640DBS-0704012016-12-25 3:47:44 PMODBC data source <PAA-KAAVTSQL1> error message for operation <SQLExecute>: <[Microsoft][SQL Server Native Client 11.0]Invalidcharacter value for cast specification>.

$GV_STARTDATE = sysdate( ); (I am using this fucntion in Script and calling this GV in Cloum mapping)

am unable to trace why the job is failing, or is DataServices SP4 not compitable with SQL SERVER 2016..

[Former Member]

HI,

I tried loading to SQL SERVER 2012 , but still facing the same issue.

I have tried other option as well

1) If i tried to load data from ORACLE( Source) to flat-file, I am facing Data Services Dump Error.

914817392RUN-0504062016-12-29 4:48:57 PMData flow <BG_FAC_NNNNN_DF> received a bad system message. Message text from the child process is 914817392RUN-0504062016-12-29 4:48:57 PM<Oracle========================================================== 914817392RUN-0504062016-12-29 4:48:57 PMCollect the following and send to Customer Support: 914817392RUN-0504062016-12-29 4:48:57 PM1. Log files(error_*, monitor_*, trace_*) associated with this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM2. Exported ATL file of this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM3. DDL statements of tables referenced in this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM4. Data to populate the tables referenced in the failed job. If not possible, get the last few rows (or sample of them) when 914817392RUN-0504062016-12-29 4:48:57 PMthe job failed. 914817392RUN-0504062016-12-29 4:48:57 PM5. Core dump, if any, generated from this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM==========================================================>. The process executing data flow <BG_FAC_NNNNN_DF> has

2) If i manually copy data from Source system to Flat-file and Load data from Flat-file to SQL SERVER , I was successfully load data.

3) If i try to load data from Oracle to SQL server , i am facing with

SQL1> error message for operation <SQLExecute>: <[Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification>.

I am using ODBC connection while connecting to Oracle(Source)

Am really having very Hard time on this issue, Can anyone help me on this

[Former Member]

we are trying to use sql server 2012.

[former_member187605]

Upgrade DS to 4.2 SP8. Or downgrade to MS SQL Server 2012. Then try again.

It makes no sense to try and run with incompatible software.

[Former Member]

I tried in different ways.

1) Declaring sysdate() to column in Query Transform

2)by declaring sysdate() and $GV_STARTDATE=to_date(sysdate(),'yyyymmdd hh24:mi:ss') to global variable

in script, tried both the methods

3) sql('DS_NEW','SELECT GETDATE() AS CurrentDateTime') in script

with the print statement i can able to see date and time, in debugging mode can see data is passing through QT but failing to insert in Target( SQL SERVER 2016)..

I am getting the same error...Is there any other way....

[former_member187605]

MS SQL Server 2016 is supported starting DS 4.2 SP8.

[Former Member]

Hi Ravi,

$GV_STARTDATE datatype is datetime only.when i print this variable i can able to see date and time but when i

use this GV in coulum i am getting the above error.

Regards

Vinay

[former_member208402]

Hi Vinay,

Please check the data type of the variable $GV_STARTDATE. If the datatype is "date" change it to "datetime".

and also use $GV_STARTDATE=to_date(sysdate(),'yyyymmdd hh24:mi:ss'). you can this format according to your requirement.

please check if this can help you.