• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Technology
    • Development
    • Hardware
    • Intune
    • Microsoft
    • Office
    • Office 365
    • Security
    • SharePoint
    • Software
    • SQL server
  • Personal development
    • Blog
    • Career
    • Freelancer
    • Knowledge
    • NEWS
    • Private
    • Thoughts
  • Consulting services
  • Contact me

Not only IT

About life, IT and other things...

Home » Technology » SQL server » Debugging SQL Server integration services solutions

Debugging SQL Server integration services solutions

November 25, 2013 By Tomasz Szulczewski Leave a Comment

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:

InternalExecutions

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.

SQLAgentProperties

Related

Filed Under: SQL server Tagged With: SQL server

About Tomasz Szulczewski

I've got more than 20 years of IT experience. IT is my passion and I am still increasing my skills. I work as a SharePoint, Office 365 and Azure architect.

Reader Interactions

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Join the Newsletter

Social media

  • Facebook
  • Instagram
  • LinkedIn
  • Twitter
  • YouTube

Search this site

Sign up for Wise accounts

Signup and earn $25

payoneer
payoneer

My latest achievement

Microsoft 365 Certified: Enterprise Administrator Expert
Microsoft 365 Certified: Enterprise Administrator Expert

Tags

Azure Azure Active Directory BizSpark Blog career Certification cloud conference edge freelance Freelancer Hardware home office InfoPath Intune Knowledge licensing Microsoft Microsoft 365 News Office Office 365 Personal development Power virtual agent Private security SharePoint SharePoint designer SharePoint online Software SQL server upwork Windows Windows 365 yammer

Footer

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

Copyright © 2022 Tomasz Szulczewski