SSIS Limitations Data Type Limitations

Consider the following data type limitations when using SQL Server Integration Services (SSIS).

• Transferring Time Data
  When transferring time data, SSIS uses the TimeSpan data type that supports precision greater than six digits. The HP Vertica ADO.NET driver treats 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 the Programmer’s Guide, specifically the section, ADO.NET Data Types.
• DATE and DATETIME
  The range for DATE and DATETIME, in order to function without errors, falls between 0001-01-01 00:00:00.0000000 and 9999-12-31 23:59:59.999999.
  In SSIS, the DATETIME type (DT_TIMESTAMP) only supports a scale up to three decimal places for the seconds. Any decimal places after that are automatically discarded. Derived column transformations can only be performed on DATETIME values between January 1, 1753 through December 31, 9999.
• Numeric
  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
• Character
  The CHAR and VARCHAR data type used in SSIS is DT_WSTR; it has a maximum length of 4000 characters.
  In SSIS, HP Vertica strings are converted to Unicode strings in SSIS to handle multi-lingual data. The strings can be converted to ASCII using a Data Conversion Task.
• Floating Point
  SQL Server does not support the values NaN, Infinity, or –Infinity. These values work when you use SSIS to transfer between HP Vertica instances, but they do not work with a SQL Server Destination.
• Scale
  Whenever a scale greater than 38 is used, SSIS replaces it with a value of 4.
• Interval
  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 fails with truncation.

Data Mapping when Using the SQL Server Import and Export Wizard

When creating an Integrated Services package (SSIS), certain data types do not automatically map correctly if you use the SQL Server Import and Export Wizard. Mapping issues have occurred when using the wizard with the SQL Server Native OLE DB Provider for SQL Server 2008 or 2012, or the SQL Server Native Client 10.0/11.0 Provider for SQL Server 2010/2012. To avoid this issue, you can manually change the type mappings using either BIDS or SSDT-BI.

Data Transfer of Certain Data Types

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 HP Vertica to SQL Server. To avoid this issue, use either BIDS or SSDT-BI when transferring data.

Batch Insert of Varbinary or Long Varbinary Data Types

If one row of a batch insert of varbinary or long varbinary data types exceeds the data type limit (i.e., 65k for varbinary, 32M for long varbinary), all rows are rejected, not 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 will 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 enclosed boolean column values in quotes. Otherwise you receive a SQL execution error (e.g., someboolean = 'true').