The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Ouch. I was recently working on a project that started with SQL Server 2008 R2 Standard Edition and eventually ended up with SQL Server 2012 Web Edition in a hosted environment. When I attempted to stand up a staging environment on the 2012 Web Edition instance I received the following error.

The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

I came to find out that the "Shared component library" feature of SSRS is only available in Standard Edition and up. I have to admit that before we moved from 2008 R2 to 2012 I reviewed the Features Supported by the Editions of SQL Server 2012 MSDN page and didn't realize that the Shared Dataset feature was a subset of the Shared component library feature. It appears that this feature is also sometimes referred to as the "Report Part Gallery" as can be seen on the Programming Features for Reporting Services Editions page, also on MSDN.

The Excel Connection Manager is not supported in the 64-bit version of SSIS

When attempting to execute a package that makes use of an Excel Connection Manager you may be presented with the following errors.

• SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "YourConnectionMgrName" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
• [Connection manager "YourConnectionMgrName" Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

The problem is that Microsoft doesn't provide a 64-bit Excel Connect Manager component (as stated in the error). This means that to run the package it must be run using the 32-bit DTEXEC.exe. When running in BIDS (Visual Studio) it's as simple as setting a project property. Below is the default property on a 64-bit machine. Change True to False to run the package.

If you're running the package under a SQL Server Agent job you must also set a similar property on the job step that runs the package. Notice the Use 32 bit runtime property. Make sure this is checked.

SSIS Error On OLE DB Command - Operand type clash: int is incompatible with date

Here's a good one - Operand type clash: int is incompatible with date.


It appears, although I have not fully confirmed, that SSIS does not play well with the new SQL Server DATE data type.


I ran into this issue when calling a stored procedure from an OLE DB Command transformation. The stored procedure had a parameter of data type DATE.


While not entirely satisfying, the quick fix for this is to change the parameter data type to DATETIME.


An issue was opened with Microsoft but they state they have not been able to reproduce the error.
http://connect.microsoft.com/SQLServer/feedback/details/628743/ssis-oledb-command-date-datatype-in-stored-procedure-sqlcommand-yields-operand-error

SSIS Error Codes (Hresults)

Ever get an Hresult error code and have no idea what it meant or how to figure out what it's related to?

The SSIS run time engine and the data flow engine are written in native code. Therefore, many errors are returned as Hresults. In case your unfamiliar with what an Hresult is, below is a Wikipedia definition for Hresult.

In the field of computer programming, the HRESULT is a data type used in many types of Microsoft technology. HRESULTs are used as function parameters and return values to describe errors and warnings in a program.

An HRESULT value has 32 bits divided into three fields: a severity code, a facility code, and an error code. The severity code indicates whether the return value represents information, warning, or error. The facility code identifies the area of the system responsible for the error. The error code is a unique number that is assigned to represent the exception. Each exception is mapped to a distinct HRESULT.

Now that you know what Hresults are, you can look up their mappings (for SSIS) using the link below.
http://msdn.microsoft.com/en-us/library/ms345164.aspx