Debugging SQL Server integration services solutions

Debugging SQL Server integration services solutions

A few months ago I wrote a post about problems when you use 32 bit driver in 64 bit SQL Server. Now I had to face another problem linked to this solution. We made an upgrade of existing SQL Server 2008 R2 version to SQL Server 2012. Unfortunately after that my SSIS package which was used to import data from Oracle database stop working… And solution to find out what’s going on wasn’t so obvious as it should be… So lets start from the begging. When I checked “View history” in SQL Sever agent I just saw an error and simple entry:

“To view the details for the execution<c/> right-click on the Integration Services Catalog<c/> and open the [All Executions] report”

Ok, we have dig dipper. Right now, after SQL server upgrade my packages are not stored in MSDB database. Instead of this we can find them in  “SSISDB” – and that was new to me 🙂 . Anyway lets move on. Tables names are obvious:

First we have to find “Execution_id”. All information are linked to this ID. To do this we have to select data from table [SSISDB].[internal].[executions] . Now, when we have “Execution_ID” we can grab other information. All steps done during execution, can be find in table [SSISDB].[internal].[operation_messages] . Important remark. Column which stores our “Execution_id” is called in this table: “operation_id”. So in my case the query statement looks like this:

SELECT TOP 1000 [operation_message_id]
,[operation_id]
,[message_time]
,[message_type]
,[message_source_type]
,[message]
,[extended_info_id]
FROM [SSISDB].[internal].[operation_messages]
where operation_id = ‘160256’

as my “execution_id” was ‘160256’. Now when you sort all rows by “operation_message_id” you will see all steps which has been executed by your SSIS page. And then I was able find obvious error:

Error: The requested OLE DB provider OraOLEDB.Oracle.1 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registred.”.

Ok, so we have the error. How to solve it? Well solution is very simple. Just go to your SQL Server agent tasks list and find your SSIS task and edit it. In package configuration go to Advanced and mark select checkbox “31-bit runtime”. And that’s all. Package will run without any problem.