Udostępnij za pośrednictwem

Integration Services Data Types

  • Artykuł

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
Changed content:
  • Updated description of the DT_DBTIMESTAMP data type.

17 July 2006
Changed content:
  • Noted issues that can occur when Boolean data types are converted to integers.
  • Noted that column values of type DT_STR or DT_WSTR that contain additional null terminators are truncated at the first null character.

5 December 2005
Changed content:
  • Added information and link to topic about mapping Integration Services data types to managed data types.
  • Added comparison of DT_DATE and DT_DBTIMESTAMP.