SSIS and an Oracle OLE DB Source

Oracle…it’s in the name right? 🙂
Well, let me tell you…it’s nothing like that!

In order to import and process Oracle source data in SSIS you required to:
– Download several Oracle 11g Client Tools (32 and 64 bits, will be explained a few rows down)

[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

You’re simply missing the 64-bits components it seems.
After some Google-ing, I found this post here:

Microsoft OLEDB Provider for Oracle and Microsoft ODBC driver for Oracle
If you are using Oracle data sources, you should migrate to the Oracle-supplied provider and driver. Microsoft OLEDB Provider for Oracle (msdaora.dll) and Microsoft ODBC driver for Oracle (msorcl32.dll) are built by using Oracle Call Interface (OCI) version 7. Oracle no longer supports applications that use OCI version 7 calls, and these technologies are deprecated

You need to use the provider from ORACLE (32-bits), more important…the 64-bits version:
64-bit ODAC 11.2 Release 4 (11.2.0.3.0) for Windows x64

Important: It is required to install the 32-bit Oracle Developer Tools for Visual Studio download first (is required for Entity Framework design-time features). The above download fully supports Entity Framework deployment, but does not contain design-time tools.

Perform a reboot after installing both toolkits!

After the reboot, verify that the Oracle providers are listed under Linked Servers -> Providers:

Another simple trick to do is to force the package to run in 32-bits mode:

The package you currently have open will (by default) run in 64 bit mode. The setting that controls this is a property on the project called Run64BitRuntime. To access this property, right-click on the Integration Services project in your solution explorer and select Properties. Then select the Debugging node in the editor. The default here is “true”, which means all the packages in this project will run in 64-bit mode. If you change this to “false”, all the packages will be run in 32-bit mode.

Another error message I’ve received was this one (don’t know when or how):

[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800700C1.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x800700C1 Description: “%1 is not a valid Win32 application.”.

To get this working in 64-bits mode, a suggestion would be to use the .NET providers instead:
OracleClient Data Provider

Windows Azure Reporting now available!

News from MSDN Blogs! (URL)

Today, we are ushering in the new era of Hybrid Cloud, bringing together the best of on-premises and cloud computing. Virtual Machines, Virtual Network, and Web Sites are now available as new preview services for Windows Azure. SQL Reporting is now Generally Available to customers, a locally redundant storage option has been added to provide additional customer choice, and several enhancements to existing Windows Azure services are now live. These updates help customers build and bring their applications to the cloud in their own unique way.

Additionally, a number of pricing and metering updates have been made to increase overall value. These changes include graduated pricing for Network, CDN and Storage; preview pricing for Windows Server and Non-Windows VMs; and a 90% reduction in Storage and CDN transaction prices.

With SQL Reporting on Azure, developers can use familiar tools such as the Business Intelligence Development Studio and SQL Server Data Tools to author reports, just as they do today when running SQL Server Reporting services on-premises. SQL Reporting on Azure provides consistent APIs to view, execute and manage reports along with rich formatting and data visualization options.

Report Formats
With SQL Reporting, you have the ability to export reports to various popular file formats including Excel, Word, HTML, PDF, XML, and CSV.

Pricing (more info here):

You can try this great service free of charge for billing periods beginning prior to August 1, 2012. Thereafter, the charge will be $0.88 per hour per reporting instance.

Well…If you’re interested, I’ll only charge you $0.60 per hour if you want to run your reports on a SSRS Instance all features supported! SQL Database (2008R2 / 2012) on a shared SQL Hotel is also possible, hosted in EasyNet‘s datacenter in Amsterdam, The Netherlands.
Contact me for more info.

MS SQL Server 2012 Error – OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).

I’ve encountered this annoying error which causes the SQL Agent Service to immediately stop after it is started.

SQL Agent was displaying ‘Agent XPs disabled‘:

Like Microsoft tells you, run this query to enable the ‘Agent XPs’-option in SQL Server:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO

…SQL Agent Service still refused to start….so back to Google! 🙂

After some Googling, I’ve found this golden tip:

This error can be due to an invalid ErrorLogFile path. Check the ErrorLogFile value under the instance registry key. The default instance key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent

It appeared that because I installed another instance earlier and uninstalled that one, somehow the SQLAGENT.OUT-file was locked. I manually deleted the file in the logging directory:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

After the deletion, I simply started the SQL Agent Service and it ran fine again! 🙂

Google also says:

This issue has been identified as a bug
It will be fixed with their next (service pack) update.


SQL Monitor on CodePlex (free standalone tool)

A few days ago someone posted a link to this very nice tool on LinkedIn:

SQL Live Monitor @ CodePlex

Description
a .NET application that provides realtime performance data on the target SQL Server instance.
No installation required, data displayed in realtime, and can also be logged to CSV for offline analysis. Will also capture SQL data for processing uing PAL.

It needs quite a bit of permissions to run, guess also registry access??

The tool itself in action: