Share this article:

Vertica Integration with Microsoft SQL Server Reporting Services: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

About SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is a platform for building enterprise-level query and reporting applications. SSRS is part of the Microsoft Business Intelligence (MSBI) suite of tools for creating BI applications for SQL Server, Microsoft's relational database.

MSBI Overview

MSBI is part of SQL Server Data Tools (SSDT), a suite of tools for creating applications within a Visual Studio shell. MSBI includes the following:

  • SQL Server Integration Services (SSIS)—Data migration, workflows, and ETL.
  • SQL Server Analysis Services (SSAS)—Business intelligence using either multidimensional or tabular modeling.
  • SQL Server Reporting Services (SSRS)—Report generation.

Vertica and SSDT Software Versions

This document provides guidance using the software listed as follows:

Software Version
Client tools
  • SSDT 2015 (If you install SSDT without Visual Studio, then the free edition of Visual Studio is included with SSDT.)
  • Visual Studio 2015
  • Microsoft SQL Server 2016 Enterprise Edition
Client Platform

Microsoft Windows Server 2012

Vertica Client

Vertica Connectivity Pack 9.1.1

Vertica Server

Vertica Server 9.1.1

Install SSRS

Download the software components from the Microsoft website and follow the installation instructions in the Microsoft documentation. Follow these steps:

  1. Download and install SQL Server.
  2. Download Visual Studio.
    1. Check the prerequisites.
    2. Follow the installation instructions.

      We recommend Visual Studio Community Edition, but you can also use Visual Studio Express Edition.

  3. Download and install SSDT.

Install the Vertica Client Drivers

SSRS can use ODBC or ADO.NET to connect to Vertica 9.1.1. SSRS cannot use OLEDB, which is supported by MSBI in addition to the other two types of drivers.

Vertica provides a connectivity pack that includes all the drivers for connecting to SQL Server components. The connectivity pack includes the drivers for both 32- and 64-bit Windows.

To download the drivers:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the driver package for Windows.
  3. Start the installer and select the options you require. All the options are selected by default.

Create a Connection Using ADO.NET or ODBC

Follow the instructions in the following topics to create a connection to Vertica from SSRS:

Create an ADO.NET Connection

  1. Open SSRS and open an existing project or create a new project.

    To create a new project, select File > New > Project.

  2. In the New Project window, select Report Server Project Wizard or Report Server Project.

    If you select Report Server Project Wizard, then SSRS starts a wizard that creates the report server project, including a data source connection and a report.

    If you select Report Server Project, then SSRS creates a project, but you must create the data source connection and the report within the project.

    When the report server project is complete, the Solution Explorer displays the report project.

  3. Right-click Shared Data Source and select Add New Data Source.

  4. On the Shared Data Source Properties page, expand the Type drop-down list. Select Vertica and click Edit.

  5. On the Connection Properties page, supply the connection information for the Vertica database.

  6. Click Test Connection.
  7. Click OK.

Create an ODBC Connection

Note

The Vertica Connectivity Pack installer provides both the 32- and the 64-bit ODBC drivers. However, only the 32-bit ODBC driver is visible within SSDT, because SSDT is a 32-bit application.

For ODBC connections, we recommend that you create a User DSN (not a System DSN), because a User DSN automatically uses the appropriate bitness for the connection.

If you create a System DSN, then you must create both a 32- and a 64-bit DSN with the same name.

  1. Open the ODBC Data Source Administrator and click Add to add a new DSN.

  2. On the Create New Data Source page, select Vertica and click Finish.

    The Vertica ODBC DSN Configuration window displays.

  3. Specify values for DSN Name, Database, Server, Port, User name, and Password.

  4. Click Test Connection.

  5. Switch to SSDT and create a new project or open an existing SSRS project.

  6. Right-click Shared Data Source and select Add New Data Source.

  7. On the Shared Data Source Properties page, expand the Type drop-down list. Select ODBC and click Edit.

  8. On the Connection Properties page, select the DSN you created.

  9. Click Test Connection.

  10. Click OK.

Deploy and Browse a Project

  1. Open the Reporting Services Configuration Manager.
  2. Select the values for Server Name and Report Server Instance, and click Connect.

  3. Click Web Service URL.

  4. Note the URL that appears in the section labeled Report Server Web Service URLs.
  5. Switch to SSDT.
  6. Right-click the project you want to deploy and select Properties.

  7. On the Properties page, select Configuration Manager > General.
  8. In the TargetServerURL text box, type the server URL that you noted earlier.

  9. Click OK.
  10. Right-click the project and select Deploy.

  11. Switch back to Reporting Services Configuration Manager and check the status.

  12. Select Web Portal URL and click the link specified on the page.

    SQL Server Reporting Services opens in the default browser.

Troubleshooting

  • If Vertica does not appear as a connection choice in SSDT, the Vertica driver may not be correctly installed.
  • When editing an existing connection, you may need to re-enter the password.

Known Limitations

SSRS connections to Vertica work only with ADO.NET and ODBC. OLEDB is not supported.

Data Type Incompatibilities with ADO.NET

  • CHAR(65000) is not supported, except for values that have a length of 65000. The following error is returned:
    H RESULT E_FAIL has been returned from a call to COM Component
  • Milliseconds are truncated in TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ data types. No error is reported.
  • INTERVAL YEAR TO MONTH is not supported. The value may display differently in a report. For example, 9-10 is displayed as 3595.00:00:00.
  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported in SSRS. The following error is returned:
    Returned data type is not valid

Data Type Incompatibility with ODBC

  • CHAR(65000) is not supported, except for values that have a length of 65000. The following error is returned:

    H RESULT E_FAIL has been returned from a call to COM Component
  • INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, and INTERVAL are not supported. The following error is returned:

    System.ArgumentException: Unknown SQL type – 112

  • Milliseconds are truncated in TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ. No error is prompted.

  • TIMEZONE is truncated in TIMETZ and TIMESTAMPTZ. No error is prompted.

  • TIME, BINARY, VARBINARY, and LONG VARBINARY are not supported in SSRS. The following error is returned:

    Returned data type is not valid

Share this article: