SQL Server Reporting Services (SSRS) & .NET

When using Reporting Services on the Server for the first time, you might find that reports won’t run in Visual Studio or on the web.

The error you get is: “Execution of user code in the .NET Framework is disabled. Enable clr enabled configuration option.”

By default .NET Framework is disabled in SQL2005 and SQL2008.

To enable it in 2005:
Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu.
2. Select "Surface Area Configuration for Features"
3. For the "CLR Integration" option, activate it and save.

To enable it in 2008:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

SSIS and an Oracle OLE DB Connection

Here is an interesting situation I came across working with SSIS and Oracle.

I have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2005(x64 bit) installed on it. I accidently installed the Oracle 10.2 client twice. I promptly removed the second Oracle client and verified I could still connect to Oracle databases in SQLPlus.

I have a SQL Server Integration Services (SSIS) solution that extracts from SQL Server and sends to an Oracle database. After my client install misadventure, when checking the Oracle Provider for OLE DB connection, I noticed something was wrong.

When trying to test connection to get an error, I got:
"oraoledb.oracle.1 provider is not registered"

Yikes, this was working before. How can I fix this and save the 13 packages that will run tonight? Surely, they will all error!

When trying to run the package anyway, I would get this error:
Code: 0xC020801C Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle Provider for OLE DB" failed with error code 0xC0202009

After looking through a myriad of other posts, it appears that the Oracle OraOLEDB10.dll file became unregistered at some point.

The fix:
START/Run then,
regsvr32 C:\oracle\product\10.2.0\client_1\BIN\OraOLEDB10.dll

Yes! The connection worked in the packages and the jobs all ran to success!