This is the multi-page printable view of this section. Click here to print.
The Vertica Microsoft Connectivity Pack for Windows provides a configuration file for you to access Microsoft Business Intelligence tools. The Connectivity Pack is installed as part of the Client Drivers and Tools for Windows.
For details about the components included with the Microsoft Connectivity Pack, see Microsoft components.
This section describes the Microsoft Business Intelligence components you can use with Microsoft Visual Studio and Microsoft SQL Server. After configuration, you can use these Microsoft components to develop business solutions using your Vertica server.
The Vertica ADO.NET driver, the Visual Studio plug-in, and the OLE DB driver allow you to integrate your Vertica server with an environment that includes Microsoft components previously installed on your system. Additional tools are also available for integration with Microsoft SQL Server.
The available drivers provide integration with the following Microsoft components:
SQL Server Data Tool - Business Intelligence (SSDT-BI) for Visual Studio 2010/2012/2013/2015 for use with SQL Server 2012, 2014, and 2016. SSDT-BI replaces BIDS for 2010, 2012, 2013, and 2015. It serves the same purpose as BIDS, providing a development environment for developing business intelligence solutions.
SQL Server Analysis Services (SSAS) for SQL Server 2012, 2014, and 2016. Use SSAS for OLAP and data mining, while using Vertica as the source for cube creation.
SQL Server Integration Services (SSIS) for SQL Server 2012, 2014, and 2016. SSIS provides SQL Server Type Mappings to map data types between Vertica and SQL Server. Use SSIS for data migration, data integration and workflow, and ETL.
The following figure displays the relationship between Microsoft components and Vertica dependencies.
SQL Server Data Tool - Business Intelligence (SSDT-BI) is a client-based application that provides a development environment for creating business solutions in Visual Studio 2012, 2013, and 2015. SSDT-BI includes project types specific to SQL Server Business Intelligence.
You can use the Visual Studio Shell Integration plug-in to browse a database from within the Visual Studio Server Explorer, allowing you to work outside of SSDT-BI development to perform tasks, such as listing tables or inserting data. When you use Visual Studio in SSDT-BI mode, you can develop business solutions using the data in your Vertica database. For example, you can create cubes or open tables.
You cannot use Microsoft Visual Studio 2012/2013/2015 with SSDT-BI development to create a SQL Server 2008 Business Intelligence solution.
BIDS or SSDT-BI includes the Analysis Services project for developing online analytical processing (OLAP) for business intelligence applications. This project type includes templates for:
Cubes
Dimensions
Data sources
Data source views
It also provides the tools for working with these objects.
You can find the OLE DB connection properties in OLE DB connection properties.
BIDS or SSDT-BI includes the Integration Services project for developing ETL solutions. This project type includes templates for:
Packages
Data sources
Data source views
It also provides the tools for working with these objects.
You can find support for using Vertica as a data source and target from both SSIS and the import/export wizard. You must install mapping files specific to Vertica on the Integration Server and BIDS or SSDT-BI workstation to enable this capability. The Vertica Client Drivers and Tools for Windows installs these mapping files as the "SQL Server Type Mappings" component(s) in both 32-bit and 64-bit versions.
BIDS or SSDT-BI includes Report projects for developing reporting solutions.
You can use Vertica as a data source for Reporting Services. The installer implements various configuration file modifications to enable this capability on both the BIDS or SSDT-BI workstation and the Reporting Services server.
This section lists compatibility issues and limitations for integrating the Microsoft Connectivity Pack with Microsoft Visual Studio and Microsoft SQL Server.
SSDT-BI is a 32-bit development environments for Analysis Services, Integration Services, and Reporting Services projects. It is not designed to run on the Itanium 64-bit architecture and thus are not installed on Itanium servers.
The SSAS Tabular Model is not supported.
If, after installing the Vertica OLE DB driver, an SSAS cube build fails, restart the SSAS service.
The following sections cover data type limitations when using SQL Server Integration Services (SSIS).
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.
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.
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
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 ConversionThe 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.
Whenever you use a scale greater than 38, SSIS replaces it with a value of 4.
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.
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.
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.
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.
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').
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:
