SSAS DSV COM error from SSDT SSAS design Data Source View

Recently I worked on a case regarding a COM error when opening the data source view (DSV) of an Analysis Services multidimensional project.

 

Error HRESULT E_FAIL has been returned from a call to a COM component. (msddsp)

 

 

Below is the call stack of the exception

 

at MSDDS.IDdsDiagram.CreateConnector(String ProgID, Boolean Visible, IDdsDiagramObject Src, IDdsDiagramObject Dest)

at Microsoft.DataWarehouse.Controls.Interop.AxMSDDS.CreateConnector(String progID, Boolean visible, IDdsDiagramObject src, IDdsDiagramObject dest)

at Microsoft.DataWarehouse.Design.ComponentDiagram.OnCreateConnector(IDdsDiagramShape startShape, IDdsDiagramShape endShape, Object logicalConnector, UInt32 color, String connectorProgID)

at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateConnector(IDdsDiagramShape startShape, IDdsDiagramShape endShape, Object logicalConnector)

at Microsoft.AnalysisServices.Design.DataSourceDiagram.CreateRelationshipConnector(DataRelation dr)

at Microsoft.AnalysisServices.Design.DataSourceDiagram.ShowOtherConnectorsOnRelatedTables(DataTable dataTable)

at Microsoft.AnalysisServices.Design.DataSourceDiagram.ShowTable(DataTable dataTable, Int32 prefX, Int32 prefY, Boolean delayArrangeTables)

at Microsoft.AnalysisServices.Design.DataSourceDiagram.AfterDeserialize()

at Microsoft.DataWarehouse.Design.ComponentDiagram.SetLayoutInfo(String xml)

at Microsoft.DataWarehouse.Design.ComponentDiagram.OnDiagramControlCreated(DdsDiagram dds)

at Microsoft.AnalysisServices.Design.DataSourceDiagram.OnDiagramControlCreated(DdsDiagram dds)

at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateDdsView(Control parentControl)

at Microsoft.AnalysisServices.Design.DataSourceDesignerView..ctor(DataSourceDesigner designer, DataSourceDiagram diagram, IComponent diagramOwnerComponent)

at Microsoft.AnalysisServices.Design.DataSourceDesignerView..ctor(DataSourceDesigner designer)

at Microsoft.AnalysisServices.Design.DataSourceDesigner.CreateDataSourceDesignerView(VsStyleToolBar pageViewToolBar)

at Microsoft.DataWarehouse.Design.EditorWindow.EnsureViewIsLoaded(EditorView view)

 

Let me explained what happens. If you have a machine installed SQL Server 2012 version of the SQL Server Data Tools, you should have this DLL file C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Bin\DDSShapes.dll

 

When you design the table relationship in a DSV, Visual Studio loads this DLL to write binary object data into the DSV metadata. The binary data for the table relationship shape object begins with "0008" in the <ddscontrol> element. Please see the screen shot below

 

 

If you have another machine that installs SQL Server 2014 version of the SQL Server Data Tools, it loads this DLL file C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin\DDSShapes.dll. If you use SQL Server 2014 version of the SQL Server Data Tools to modify the DSV, the binary data starts with "000a".

 

 

If you try to use SQL Server 2012 version of the SQL Server Data Tool to open the DSV modified by the SQL Server 2014 version of the SQL Server Data Tool, you gets this COM exception. The reason is, this leading 4 characters of binary data is the version of ATL used to compile the DDSShapes.dll. When this DLL reads the binary data, it check if the version of the ATL that was used to save the data, making sure it is below the version recognized by the DDSShapes.dll. "000a" is a higher value than "0008", so the SQL Server 2012 version of the SQL Server Data Tool throws an exception.

 

The solution is to install the SQL Server 2014 version of the SQL Server Data Tool from https://msdn.microsoft.com/en-us/data/hh297027 . You should get the new version of the DDSShapes.dll in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin.

 

After explicitly registering the DDSShapes.dll from C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin (using regsvr32), the problem should be resolved. Please see the command below

 

 

This means, if there are multiple SSAS developers working on the same SSAS database or project, and one of the developers starts to use SQL Server 2014 version of the SQL Server Data Tools, all the developers should install the SQL Server 2014 version of the SQL Server Data Tools.