SQL and Analysis Management Objects

Overview

When trying to connect the Jet Data Manager to a database on a remote server the following error message may appear:

Could not load file or assembly 'Microsoft SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Cause:

The Jet Data Manager uses SQL Server Management Objects (SMO) and Analysis Management Objects (AMO), which is a collection of objects that are designed for programming all aspects of Microsoft SQL Server.

To ensure proper installation:

• SMO/AMO objects need to be of the same version (e.g.: SQL 2005, SQL 2008, etc.) as the destination SQL Server (The server where the project repository, stage and data warehouse reside)
• SMO/AMO objects need to have the same bit rate (32-bit or 64-bit) as the Jet Data Manager

Before Installing SMO/AMO Objects

To properly install SMO/AMO objects you may need to install these additional components on the server which is running Jet Data Manager.

• Microsoft Core XML Services (MSXML)
• Microsoft SQL Server Native Client
• Microsoft SQL Server System CLR Types

These components are part of the SQL Server feature pack, which is a collection of stand-alone installation packages that provide additional value for SQL Server.  Make sure you select the feature pack that corresponds to your version of SQL Server.

Links to SQL Server feature packs (please note this is not an exhaustive list, you'll need to find the one that corresponds to your version and service pack level, found by searching for SQL Server XX XX Feature Pack):

Note: SMO/AMO objects will not install properly until all additional components are installed. The additional components may differ between SQL Server versions. (For example SQL Server 2005 doesn't require CLR Types)

Installing SMO/AMO Objects

• Installing SMO/AMO Objects

  To install SMO/AMO objects find the feature pack that corresponds to your version of SQL Server. Then choose the SMO/AMO Objects that correspond to your version of the Jet Data Manager (32-bit or 64-bit).  In this example we are downloading the 64-bit version.

  These components are required for the JDM to work correctly:

  • AMO Objects (SQL_AS_AMO.msi)
  • SMO Objects (SharedManagementObjects.msi)
  • SQL CLR Types (SQLSysClrTypes.msi)

  

  These additional objects are prerequisites and may be required, as well:
  • Microsoft Core XML Services (MSXML)
  • Microsoft SQL Server Native Client
  • Microsoft SQL Server System CLR Types
  • SMO Objects

  Once the files have been downloaded, run the setup files and follow the installation wizard.

Enabling ADO.NET

Once SMO/AMO objects have been installed on the server you will want to enable ADO.NET.

• Right-click the top node of the project tree and select Edit Project.

  

• Uncheck the box for Use Integration Services (SSIS).

  

• Click OK.