Share this article:

Vertica Integration with Microsoft SQL Server Integration Services: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested these other versions

About this Document

This document explains how to connect to Vertica with Microsoft SQL Server Integration Services (SSIS) using the software versions listed in Vertica and SSDT Software Versions.

A Vertica connection in an SSIS application can serve as either a data source or a data target or both.

SSIS Overview

SSIS is a platform for building enterprise-level data integration and transformation applications. SSIS 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 Tool (MSDT), a tool 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 (MOLAP) or relational (ROLAP) analysis. ROLAP mode is usually called tabular.
  • 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 8.0.x and 8.1.x, a set of drivers for connecting to SQL Server components.

Vertica Server

Vertica Server 8.0.x and 8.1.x

SSIS and Vertica Driver Compatibility

We have tested SSIS and Visual Studio in combination with different versions of our Vertica drivers. The supported combinations are shown in the following compatibility matrix.

SSIS and Visual Studio Vertica 8.0 Vertica 8.1.0-5 Vertica 8.1.1
SSIS 2012
Visual Studio 2010
Supports ADO.NET and ODBC Supports ADO.NET and ODBC Supports ADO.NET and ODBC
SSIS 2014
Visual Studio 2012
Supports ADO.NET and ODBC Supports ADO.NET and ODBC Supports ADO.NET and ODBC
SSIS 2016
Visual Studio 2015
Supports ADO.NET and ODBC Supports ADO.NET and ODBC Supports ADO.NET and ODBC

Install SSIS

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 2016.
  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

SSIS can use ODBC or ADO.NET to connect to Vertica 8.0 and 8.1. SSIS 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. Log in to my.vertica.com.
  2. Navigate to the downloads page for client drivers:

    https://my.vertica.com/download/vertica/client-drivers/

  3. Download the driver package for Windows.
  4. Start the installer and select the options you require.

Create a Connection Using ADO.NET or ODBC

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

Create an ADO.NET Connection

  1. Open SSDT and select File > New > Project.
  2. To create a new Integration project, select Template > Business Intelligence > Integration Servicesin the left pane and Integration Services Project in the right pane.

    When the new project is created, the Solution Explorer window displays in the right side of the SSDT Designer.

  3. Right-click Connection Manager and select New Connection Manager.

  4. In the Add SSIS Connection Manager window, select ADO.NET and click Add.

  5. In the Configure ADO.NET Connection Manager window, click New.

    The Connection Manager window displays.

  6. Expand the Provider drop-down list.

    Under .NET Provider, select Vertica Data Provider.

  7. Specify values for Database, Host, User, Password, and Port.

  8. Click Test Connection.

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. 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 to create a new project or open an existing project.

  6. Right-click Connection Managers and select New Connection Manager to open the Add SSIS Connection Manager window.

  7. Select ODBC and click Add.
  8. Select Connection manager for ODBC connections.

  9. Click New to open the Connection Manager window.

  10. In the Connection Manager window, expand theUse user or system data source name drop-down list and select the ODBC DSN that you created.

  11. Click Test Connection.

Performance with ODBC and ADO.NET

Our testing has shown that ODBC is the more performant option for connecting SSIS to Vertica. You can tune the BatchSize property of ODBC to increase efficiency. BatchSize controls the loading of data in batches for both Source and Destination components in SSIS. By default, BatchSize is 1000.

We have tested the Vertica ADO.NET and ODBC drivers with a 1.2 million row table where Vertica was both the Source and the Destination. With the ODBC driver, using the default batch size, SSIS loaded the data in 36 seconds. With the ADO.NET driver, SSIS loaded the data in 82 seconds. When we changed the value of BatchSize to 50,000, the time to load the 1.2 million rows decreased from 36 to only 16 seconds.

The same value of BatchSize may yield different performance results on different machines, since resources available for any task vary. A value of BatchSize that allows data to accumulate in memory improves performance. A value of BatchSize that is greater than the available memory for the drive buffer degrades performance.

Troubleshooting

If the Vertica connection option does not appear in SSDT, then the Vertica driver may not be correctly installed. Try editing the connection and re-entering the password.

Known Limitations

  • SSIS connections to Vertica can use ADO.NET or ODBC, but OLEDB is not supported.

Data Type Incompatibilities with ADO.NET

  • SSIS loads null instead of empty for VARCHAR and LONGVARCHAR data types.
  • SSIS supports numeric data types up to 29 digits of precision. With higher precisions, SSIS returns the following error:
    Row <Row_number> was rejected by the server
  • SSIS does not support the Vertica INTERVAL data type. When attempting to read an INTERVAL data type, SSIS returns the following error:
  • <ADO.NET_Source> failed because truncation occurred, and the truncation row disposition on <ADO NET Source.Outputs[ADO NET Source Output].Columns[DayToSecond]> specifies failure on truncation. 
  • SSIS does not support the INTERVAL YEAR TO MONTH data type.When attempting to read Vertica data of this type, SSIS returns the following error:
  • Error: Invalid conversion from Interval_Day_To_Second to Interval_Year_To_Month.”
  • SSIS rounds off Vertica TIME and TIMESTAMP data to the nearest millisecond.

Data Type Incompatibilities with ODBC

  • SSIS loads null instead of empty for VARCHAR and LONGVARCHAR data types.
  • SSIS supports numeric data types up to 38 digits of precision. With higher precisions, SSIS returns the following error:
  • Numeric value out of range
  • SSIS rounds off Vertica TIME data to the nearest millisecond.
  • SSIS truncates Vertica TIMETZ data to millisecond and returns the wrong time zone.
  • SSIS does not support the maximum length of LONGVARBINARY data. When attempting to read data of this type, SSIS returns this error::
  • Failed: String data is too big for driver's data buffer. Native Error Code: 2052. ERROR 2052:  COPY: Row size 32320356 is too large.

For More Information

Share this article: