Results 1 to 1 of 1

Thread: Troubleshooting Microsoft Connector for Oracle in SSIS

  1. #1
    Adeeb Mass'ad is offline Support Manager
    Join Date
    Aug 2006
    Jaffa of Nazareth
    Rep Power

    Troubleshooting Microsoft Connector for Oracle in SSIS

    How to troubleshoot Microsoft Connector for Oracle by Attunity at run-time?


    This step by step article describes how to troubleshoot and analyze run-time failures related to the Microsoft Connector for Oracle by Attunity.

    The debugging process is dependent on the logging facility which SSIS provides for the external providers. Using the verbose log files is necessary when the other debugging facilities of the Microsoft SQL Server Business Intelligence Development Studio did not help, or the nature of the problems is related to the Data Provider.

    STEP 1 - Eliminate the common problems

    Check for the common problems and error messages which may be found in the Event Viewer.
    Permission problems at run-time.

    Check the relevant error message and confirm that the SQLServer service and the Agent has the required permissions in the specified account.

    Problem running a package on 64Bit platform, a package which worked on 32Bit platform.

    - Make sure you are using the 64Bit Connector, for the Microsoft Connector for Oracle, there are special installation kits for Win X64 and Itanium IA64.

    - If you are calling DTS Packages using the Execute DTS 2000 Package task to run a SQLServer 2000 DTS package, you must run the package in 32Bit.
    General errors in the Event Viewer.

    Always check the Windows Event Viewer for general error messages. If the problem is indeed related to the Data Provider, proceed to STEP 2.
    STEP 2 - Using the Logging Facility

    The Microsoft Connector for Oracle outputs meaningful error messages to SSIS, however, there are cases where there is a need for verbose debugging log file, which can show the complete lifecycle of the interaction with the Connector and the back-end database.

    In these cases SSIS provides a complete logging facility and several Logging Providers.

    To enable logging, perform the following steps:
    1. In the SQL Server Business Intelligence Development Studio, open the Package where you want to enable logging.

    2. Either click on Control Flow tab and Right-Click and click on Logging, OR from the SSIS menu click on Logging.

    Attachment 137

    3. In the "Providers and Logs" tab, click Add and choose a Logging Provider, for example by choosing the "SSIS log provider for Text files" you can output the logging to a simple text file in your file system:

    Attachment 138

    - Click Add to add the selected provider.

    - Click on the checkbox on the left of the provider to enable it.

    4. Configure the Logging Provider by clicking on the Configuration column. You can either create a "New Connection" or use an existing one.

    For the Text provider, you can choose to create a new text file and output the logging to it. Another option may be to always append to an existing file.

    Attachment 139
    5. You can define several Log file Providers to output to multiple log files of different formats.

    6. Selecting the Diagnostic level for debugging.

    - Click on the Details tab, and select which events you want to log.

    - For the Oracle Connector choose the Diagnostic debugging level, which will output among others the important interactions with the OCI interface of Oracle.

    - Note that you can select other events to be logged as needed, for example OnError/OnInformation/OnWarning, ...

    Attachment 140

    7. You may click Save and save the current configuration.

    8. Click OK.

    9. To save the changes to the package click on the Save icon, or from the File menu click on "Save Selected Items".

    10. Execute the problematic package or the package which you want to debug and review the output log file.

    STEP 3 - Understanding the Log file

    The verbose log file contains the details and interaction of the different components in your package. Quickly reviewing the log file may show the user problems which could be fixed without the need for Support.

    Many general problems related to the SQL Query/Backend Database/SSIS may be solved by reviewing the log file. If for any reason the user cannot understand the cause of the failure a Support Call should be made in order to get an explanation of the failure.

    Analysis Tips

    - The log contains many Pre/Post and Enter/Exit messages. Make sure for every request there is a response, for example:

    2:46:34 PM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IRowset::GetData'.

    2:46:34 PM,0,0x,ExternalRequest_post: 'IRowset::GetData succeeded'. The external request has completed.

    - For OCI calls you will see Enter/Exit pairs as follows:

    2:46:36 PM,0,0x,*Enter > OCIAttrGet
    2:46:36 PM,0,0x,*Exit < OCIAttrGet
    • SQL Server Business Intelligence Development Studio 2008
    • Microsoft SSIS Connector for Oracle by Attunity
    • Microsoft SSIS Connector for Oracle by Attunity - Itanium IA64
    • Microsoft SSIS Connector for Oracle by Attunity X64
    Last edited by Adeeb Mass'ad; 09-02-2008 at 09:56 AM.
    To Find Out more About Attunity Technology:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts