none
ORA-00911: invalid character

    Question

  • I have a simple Execute SQL Task at the beginning of my package.
    This task connects to an Oracle database and issues the following statement:

    SELECT 1 FROM DUAL

    I do this to check if the connection to the source database is working properly, before I go on and truncate everything in the destination database.

    The package runs succesfully in BIDS and on the server in a SQL Server Agent job.
    However, if I run the package in BIDS, I get the following warning:

    Warning: 0x0 at (SQL) Test Oracle Connection: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    No errors and everything is joyfully green.
    If I run the package at the server, the following is logged:

    * Executing the query "SELECT 1 FROM DUAL;" failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    * ORA-00911: invalid character

    * Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    So there are errors, BIDS just doesn't show them.

    The job history shows the following:

    Started:  22:30:00  Error: 2012-04-14 22:30:16.26     Code: 0x00000000     Source: (SQL) Test Oracle Connection      Description: ORA-00911: invalid character  End Error  Error: 2012-04-14 22:30:16.30     Code: 0xC002F210     Source: (SQL) Test Oracle Connection Execute SQL Task     Description: Executing the query "SELECT 1 FROM DUAL;" failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  22:30:00  Finished: 22:30:16  Elapsed:  15.6 seconds.  The package executed successfully.  The step succeeded.

    Conclusion: despite the error/failure, the package still succeeds. Bit of a strange behaviour, and I'm curious why.

    The SQL statement itself runs without a problem directly on Oracle using SQL Explorer.
    The only threads I found about these topic suggest to remove the semicolon from the end of the statement if there is any, but this doesn't have any impact on my SSIS package.

    Any ideas?


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Tuesday, April 17, 2012 5:46 AM

Answers

  • Hi Olaf, thanks for your reply.

    A bit of an update:
    first I had a semicolon terminating the statement, and apparently this caused the ORA-00911: invalid character error.
    But, when I remove the semicolon I still get the warning

    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    I tried your statement, but it returns the same warning. I use the Oracle OLE DB provider (so not the Microsoft OLE DB provider for Oracle).

    Anyway, thanks for the input!


    MCTS, MCITP - Please mark posts as answered where appropriate.


    Tuesday, April 17, 2012 6:34 AM

All replies

  • Hello Koen,

    I use a similar SQL statement to test Oracle connectivity and it works fine in both BIDS and as a package. The only difference in my script is I use a alias for the result column, may this causes the problem with your script.

    SELECT 'Testing connection ...' AS TestResult FROM dual


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, April 17, 2012 6:21 AM
  • Hi Olaf, thanks for your reply.

    A bit of an update:
    first I had a semicolon terminating the statement, and apparently this caused the ORA-00911: invalid character error.
    But, when I remove the semicolon I still get the warning

    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    I tried your statement, but it returns the same warning. I use the Oracle OLE DB provider (so not the Microsoft OLE DB provider for Oracle).

    Anyway, thanks for the input!


    MCTS, MCITP - Please mark posts as answered where appropriate.


    Tuesday, April 17, 2012 6:34 AM
  • Update: most of the resources I find say there's should be something wrong with the connectionstring.
    (http://support.microsoft.com/kb/269495/en-us)

    However, it looks fine to me.

    Data Source=myDataSource;User ID=myUser;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;

    The password is stored in a configuration.


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Tuesday, April 17, 2012 8:07 AM