Integration Services Data Types
When data enters a data flow in a package, the source that extracts the data converts the data to an Integration Services data type. Numeric data is assigned a numeric data type, string data is assigned a character data type, and dates are assigned a date data type. Other data, such as GUIDs and Binary Large Object Blocks (BLOBs), are also assigned appropriate Integration Services data types. If data has a data type that is not convertible to an Integration Services data type, an error occurs.
Some data flow components convert data types between the Integration Services data types and the managed data types of the Microsoft .NET Framework. For more information about the mapping between Integration Services and managed data types, see Mapping Data Types in the Data Flow.
The following table lists the Integration Services data types.
| Data type | Description |
|---|---|
DT_BOOL |
A Boolean value. |
DT_BYTES |
A binary data value. The length is variable and the maximum length is 8000 bytes. |
DT_CY |
A currency value. This data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19. |
DT_DATE |
A date structure that consists of year, month, day, and hour. The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE. On the other hand, DT_DBTIMESTAMP is represented by a structure that has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has no limits on ranges of the dates it can present. |
DT_DBDATE |
A date structure that consists of year, month, and day. |
DT_DBTIME |
A time structure that consists of hour, minute, and second. |
DT_DBTIMESTAMP |
A timestamp structure that consists of year, month, day, hour, minute, second, and millisecond. |
DT_DECIMAL |
An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29. |
DT_FILETIME |
A 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601. |
DT_GUID |
A globally unique identifier (GUID). |
DT_I1 |
A one-byte, signed integer. |
DT_I2 |
A two-byte, signed integer. |
DT_I4 |
A four-byte, signed integer. |
DT_I8 |
An eight-byte, signed integer. |
DT_NUMERIC |
An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 - 38, and a maximum precision of 38. |
DT_R4 |
A single-precision floating-point value. |
DT_R8 |
A double-precision floating-point value. |
DT_STR |
A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.) |
DT_UI1 |
A one-byte, unsigned integer. |
DT_UI2 |
A two-byte, unsigned integer. |
DT_UI4 |
A four-byte, unsigned integer. |
DT_UI8 |
An eight-byte, unsigned integer. |
DT_WSTR |
A null-terminated Unicode character string with a maximum length of 4000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.) |
DT_IMAGE |
A binary value with a maximum size of 231-1 (2,147,483,647) bytes. . |
DT_NTEXT |
A Unicode character string with a maximum length of 230 - 1 (1,073,741,823) characters. |
DT_TEXT |
An ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647) characters. |
Changing the Data Type of Columns
If the data in a column does not require the full width allocated by the source data type, you might want to change the data type of the column. Making each data row as narrow as possible helps optimize performance when transferring data because the narrower each row is, the faster the data is moved from source to destination.
Integration Services includes a complete set of numeric data types, so that you can match the data type closely to the size of the data. For example, if the values in a column with a DT_UI8 data type are always integers between 0 and 3000, you can change the data type to DT_UI2. Similarly, if a column with the DT_CY data type can meet the package data requirements by using an integer data type instead, you can change the data type to DT_I4.
You can also change the data type on a column with date/time data to extract the day or the time part of the data. For example, if you change the data type of a column from DT_DBTIMESTAMP to DT_DBTIME, only the hour, minute, and second part of the value is available.
You can change the data type of a column in the following ways:
- Using a transformation to replace column values with the results of an expression, or to create a copy of a column that has a different data type than the original column. For more information, see Derived Column Transformation.
- Using a transformation to cast the data type of a column from one data type to a different data type. For more information, see Data Conversion Transformation.
Note
Boolean values are logical values, not numbers. Although Boolean values may be displayed as numbers in some environments, they are not stored as numbers, and various programming languages represent Boolean values as numeric values differently, as do the .NET Framework methods. For example, the conversion functions available in Visual Basic convert True to -1; however, the System.Convert.ToInt32 method in the .NET Framework converts True to +1. The Integration Services Expression Language converts True to -1. To avoid errors or unexpected results, you should not write code that relies on particular numeric values for True and False. Wherever possible, you should restrict usage of Boolean variables to the logical values for which they are designed.
Mapping Integration Services Data Types to Database Data Types
The following table provides guidance on mapping the data types used by certain databases to Integration Services data types. These mappings are summarized from the mapping files used by the SQL Server Import and Export Wizard when it imports data from these sources. For more information on these mapping files, see Creating Packages Using the SQL Server Import and Export Wizard.
Important
These mappings are not intended to represent strict equivalency, but only to provide guidance. In certain situations, you may need to use a different data type than the one shown in this table.
| Data Type | SQL Server (SQLOLEDB; SQLNCLI) | SQL Server (SqlClient) | Jet | Oracle (MSDAORA) | Oracle (OracleClient) | DB2 (DB2OLEDB) | DB2 (IBMDADB2) |
|---|---|---|---|---|---|---|---|
DT_BOOL |
bit |
bit |
Bit |
||||
DT_BYTES |
binary, varbinary, timestamp |
binary, varbinary, timestamp |
BigBinary, VarBinary |
RAW |
RAW |
||
DT_CY |
smallmoney, money |
smallmoney, money |
Currency |
||||
DT_DATE |
|||||||
DT_DBDATE |
|||||||
DT_DBTIME |
|||||||
DT_DBTIMESTAMP |
datetime, smalldatetime |
datetime, smalldatetime |
DateTime |
TIMESTAMP, DATE, INTERVAL |
TIMESTAMP, DATE, INTERVAL |
TIME, TIMESTAMP, DATE |
TIME, TIMESTAMP, DATE |
DT_DECIMAL |
|||||||
DT_FILETIME |
|||||||
DT_GUID |
uniqueidentifier |
uniqueidentifier |
GUID |
||||
DT_I1 |
|||||||
DT_I2 |
smallint |
smallint |
Short |
SMALLINT |
SMALLINT |
||
DT_I4 |
int |
int |
Long |
INTEGER |
INTEGER |
||
DT_I8 |
bigint |
bigint |
BIGINT |
BIGINT |
|||
DT_NUMERIC |
decimal, numeric |
decimal, numeric |
Decimal |
NUMBER, INT |
NUMBER, INT |
DECIMAL, NUMERIC |
DECIMAL, NUMERIC |
DT_R4 |
real |
real |
Single |
REAL |
REAL |
||
DT_R8 |
float |
float |
Double |
FLOAT, REAL |
FLOAT, REAL |
FLOAT, DOUBLE |
FLOAT, DOUBLE |
DT_STR |
char, varchar |
VarChar |
CHAR, ROWID, VARCHAR2 |
CHAR, VARCHAR |
CHAR, VARCHAR |
||
DT_UI1 |
tinyint |
tinyint |
Byte |
||||
DT_UI2 |
|||||||
DT_UI4 |
|||||||
DT_UI8 |
|||||||
DT_WSTR |
nchar, nvarchar, sql_variant, xml |
char, varchar, nchar, nvarchar, sql_variant, xml |
LongText |
NVARCHAR2, NCHAR |
CHAR, ROWID, VARCHAR2, NVARCHAR2, NCHAR |
GRAPHIC, VARGRAPHIC |
GRAPHIC, VARGRAPHIC |
DT_IMAGE |
image |
image |
LongBinary |
LONG RAW, BLOB, LOBLOCATOR, BFILE, VARGRAPHIC, LONG VARGRAPHIC, user-defined |
LONG RAW, BLOB, LOBLOCATOR, BFILE, VARGRAPHIC, LONG VARGRAPHIC, user-defined |
CHAR () FOR BIT DATA, VARCHAR () FOR BIT DATA |
CHAR () FOR BIT DATA, VARCHAR () FOR BIT DATA, BLOB |
DT_NTEXT |
ntext |
text, ntext |
NCLOB, NVARCHAR, TEXT |
LONG, CLOB, NCLOB, NVARCHAR, TEXT |
LONG VARCHAR, NCHAR, NVARCHAR, TEXT |
LONG VARCHAR, DBCLOB, NCHAR, NVARCHAR, TEXT |
|
DT_TEXT |
text |
CLOB, LONG |
LONG VARCHAR FOR BIT DATA |
LONG VARCHAR FOR BIT DATA, CLOB |
For information on mapping data types in the data flow, see Mapping Data Types in the Data Flow.
See Also
Concepts
Working with Data in Data Flows
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|
17 July 2006 |
|
5 December 2005 |
|