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.
The maximum and minimum decimal allowed is:
For example, if the scale is 16, the range of values is:
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.
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.
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.
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.
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').