DSXchange

I got the same error as Sonia in this post and cannot find any workaround.

@Sonia, can you please put the solution you have found to solve this issue?

It may be that you have an ODBC driver for Excel, even though Excel itself is not installed. In that case you can use an ODBC stage.

ray.wurlod wrote:It may be that you have an ODBC driver for Excel, even though Excel itself is not installed. In that case you can use an ODBC stage. ...

I'a currently using an ODBC Stage, but I get this error and I have tried to change the data type in the ODBC Stage but the problem remains.

The full error is the following:

Ext2Excel..Transformer_1: |Ext2Excel..ODBC_0.DSLink2: DSD.BCIOpenW call to function SQLPrepare failed.
SELECT MESSAGETYPEID,MESSAGESEVERITY,MESSAGETEXT FROM `Tabelle1$`
SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered|

That thread was eight years old and Sonia hasn't posted in four. Off to your own post.

The original post was for Parallel jobs but your posts are always for Server jobs... is this a Server job question as well?

Yes . It is server job . DataStage 8.1.2

Thanks... corrected and in the proper forum now.

What format types have you specified within the Excel sheet?
Try make all columns (at least those you're extracting anyway) type Text (within Excel) and see if it works

No , it doesnt work. Always I receive the same error

Ext2Excel..ODBC_0.DSLink2: DSD.BCIOpenW call to function SQLPrepare failed.
SELECT MESSAGETYPEID FROM `Tabelle1$`
SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered

Any other tips?

Yeah, forget Excel... right royal pita!

Do a table import of your ODBC DSN and use the definition it creates in your job.

While I would truly hope it isn't the cause, you could also try

I have done an ODBC Table Definition Import in DS and used in ODBC Stage to write to it, in oder to have an Excel file with the data from my Hash Table.

Hash_Table --> Transformer --> ODBC Stage (for Excel)

Anyhow can't get it work due to the Data Type error

For what do you suggest a Select stmt when I want to Insert?

Not much really, we had to fiddle around a bit ourselves so can't be sure which bits were right. Did find it liked those funny quotes though (the one below the ~), just make sure the column names match the first row of the excel sheet (your Tabelle1 is the name of a sheet rather than a named section I assume?)

Try to check your odbc drive connection and if your execl may be correct, delete and try to do one more time.

Thanks a lot for all suggestions. I will try out and see if I can make it work. The, I will post my results.

Anyhow please, let me know if anybody has any other hint.