Compatibility issues and limitations

• 1: BIDS and SSDT-BI limitations
• 2: SSAS limitations
• 3: SSIS data type limitations
• 4: SSRS limitations

1 - BIDS and SSDT-BI limitations

BIDS and SSDT-BI are 32-bit development environments for Analysis Services, Integration Services, and Reporting Services projects. They are not designed to run on the Itanium 64-bit architecture and thus are not installed on Itanium servers.

2 - SSAS limitations

• The SSAS Tabular Model is not supported.

• If, after installing the Vertica OLE DB driver, an SSAS cube build fails, restart the SSAS service.

3 - SSIS data type limitations

The following sections cover data type limitations when using SQL Server Integration Services (SSIS).

Time data transfer

When transferring time data, SSIS uses the TimeSpan data type that supports precision greater than six digits. The Vertica ADO.NET driver translates TimeSpan as an Interval data type that supports up to six digits. The Interval type is not converted to the TimeSpan type during transfer. As a result, if the time value has a precision of more than six digits, the data is truncated, not rounded.

For information on ADO.NET data types, refer to ADO.NET data types.

DATE and DATETIME precision

To function without errors, DATE and DATETIME have a range from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.999999.

In SSIS, the DATETIME type (DT_TIMESTAMP) supports only a scale up to three decimal places for seconds. Any decimal places after that are automatically discarded. You can perform derived column transformations only on DATETIME values between January 1, 1753 through December 31, 9999.

Numeric precision

The maximum and minimum decimal allowed is:

• Max: +79,228,162,514,264,337,593,543,950,335

• Min: -79,228,162,514,264,337,593,543,950,335

For example, if the scale is 16, the range of values is:

+/- 7,922,816,251,426.4337593543950335

The valid scale range is any number that is smaller than 29 and greater than 38. Using a scale between 29 and 38 does not generate an error.

See: http://msdn.microsoft.com/en-us/library/system.decimal.maxvalue.aspx

Unsupported floating point values

SQL Server does not support NaN, Infinity, or –Infinity values. These values are supported when you use SSIS to transfer between Vertica instances, but they are not supported with a SQL Server Destination.

String Conversion

The CHAR and VARCHAR data types used in SSIS are DT_WSTR, with a maximum length of 4000 characters.

In SSIS, Vertica strings are converted to Unicode strings in SSIS to handle multi-lingual data. You can convert these strings to ASCII using a Data Conversion Task.

Scale

Whenever you use a scale greater than 38, SSIS replaces it with a value of 4.

Interval conversion

SSIS does not support interval types. It converts them to TIME and strips off the day component. Any package that has interval types greater than a day returns incorrect results.

Data mapping issues with SQL server import and export wizard

When you create an Integrated Services package (SSIS) using the SQL Server Import and Export Wizard, certain data types do not automatically map correctly. A mapping issue can occur when you use the wizard with:

• SQL Server Native OLE DB Provider for SQL Server 2008 or 2012

• SQL Server Native Client 10.0/11.0 Provider for SQL Server 2010/2012

To avoid this issue, manually change the type mappings with BIDS or SSDT-BI.

Data transfer failures

When using an Integrated Services package (SSIS) with the SQL Server OLE DB Provider for SQL Server 2008 or 2012, certain data type transfers can fail when transferring from Vertica to SQL Server. To avoid this issue, use either BIDS or SSDT-BI when transferring data.

Batch insert of VARBINARY/LONG VARBINARY data types

Sometimes, one row of a batch insert of VARBINARY or LONG VARBINARY data types exceeds the data type limit:

• VARBINARY: 65 KB

• LONG VARBINARY: 32 MB

In such cases, all rows are rejected, rather than just the one row whose length exceeds the type limit. The batch insert fails with the message "row(s) are rejected".

To avoid this issue, use a predicate to filter out rows from the source that do not fit into the receiving database.

Boolean queries in SQL server query designer

When issuing a Boolean query in SQL Server Query Designer, you must enclose Boolean column values in quotes. Otherwise, you receive a SQL execution error (for example, someboolean = 'true').

4 - SSRS limitations

Data Connection Wizard Workaround

The SSRS Report Wizard provides a data connection wizard. After you select the wizard and enter all the connection information, the OK button is disabled. You cannot save your work and continue. The workaround is to not use the wizard and to use the following panel instead:

Report Wizard - Query Designer

Vertica uses the Report Wizard's Generic Query Designer. Other data sources use a Graphical Query Designer that supports building queries visually. The Graphical Query Designer is a part of a package called Visual Data Tools (VDT). The Graphical Query Designer works only with Generic OLE DB providers and the built-in providers. You cannot use it with the Vertica Data Provider.

Report Builder

Report Builder is a web-based report design tool. It does not support creating reports using custom data extensions, so you cannot use it with Vertica. When you create a report using Report Builder, existing Vertica data sources appear in the list of available data sources. However, choosing a Vertica data source causes an error.

Schema Name Not Automatically Provided when Mapping Vertica Destination

Currently, when you map a Vertica destination, the schema name is not automatically provided. You must enter it manually or pick it from the drop-down menu as follows: