DSXchange

I am attempting to read a single data value from Excel (Column heading Ready_ Status) with DRS_connector stage

Connection string
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=790;Dbq=F:\Data\EDW_DEV\Import\ETL_DATA.xls.ETL_Data;DefaultDir=F:\Data\EDW_DEV\Import;READONLY=TRUE;FirstRowHasNames=1

SQL Statement
SELECT Ready_Status FROM F:\Data\EDW_DEV\Import\ETL_DATA.xls

Test job design is DRS_Connector - Transform - Peek

The Errors I get

tfrMap: Error when checking operator: Could not find input field "Ready_Status".

DRS_Connector_3: When checking operator: The modify operator has a binding for the non-existent output field "Ready_Status".

Any advice on what I need to do to solve this problem

Thanking you all in advance

Try quoting the column name and the table name.

Quoting did not change the error message.

Could someone please provide an example of the text that enter on each the properties page and columns page of the DRS_connector stage - An example connecting to an Excel odbc would be great but any working examples would be appreciated

Thanking you all in advance

Can you set up the worksheet as a linked table in MS Access and query it from there? This would be a useful diagnostic check.

In all my runs of my test job the error I get is

DRS_Connector_3: Error when checking operator: Caught unknown exception during wrapDescribeOperator().

As part of testing I have changed from using a connection_string to using dsn and the test job work

So the issue now focusses DRS stage using a connection string

Any advice on using a connection string with this stage

Thanking you all in advance

In the DSR stage, what is happening when you trigger the Test in Connection.

Can you view the data from DSR?

DS User

Doing the Test connection on the stage I get "The connection failed" - other times I will also get doing this test "system resources exceeded" - I have been at other sites where the test connection option alway failed - the work around - option was not to be used

I have now changed from using a connection string to using DSN - The test job will run. But my goal of this test is to use the DSR stage with a conenction string - has anyone done this.

Thanking you all in advance

I read somewhere and aware that i need to use ODBC to read .xls in Datastage, but today is the first time i connected the .xls file using Datastage.

Steps:

In Windows ODBC
1. Create a SYSTEM DSN (Not User DSN - Thanks Ray. I saw your comment)
1a. Choose the proper Excel Version
1b. Select the work book (using Browse)


Now come to Datastage:

Notes: I used ODBC Connector Stage
2. Use the ODBC Connector Stage
3. Pass the DSN name (Eg: TEST_XLS)
3a. No need to pass username and password
4. Generated SQL = No
5. Import the metadata using ODBC & use that metadata.
5a. Remember the metadata name. In my case TEST$
6. In the select statement pass select * from `TEST$`
6a: Note: This is not ' it is `
7. Just run the job.

I can't view the data, but when i run the job i can see the data in the output.

Hope this will help of someone is trying to connect .xsl file using Datastage.

DS User

Update:

I tried with DSR and it is also working fine.


1. choose the Database type as ODBC and rest of the steps are same as ODBC stage mentioned in the previous comment.

DS User

I can't understand your question. sorry.

DS User

That would be a question for your official support provider, perhaps there is a patch available.