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 Working with Data Types in the Data Flow.
The following table lists the Integration Services data types. Some of the data types in the table have precision and scale information that applies to them. For more information about precision and scale, see Precision, Scale, and Length (Transact-SQL).
|
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 digits.
|
|
DT_DATE
|
A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.
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 internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger 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_DBTIME2
|
A time structure that consists of hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.
|
|
DT_DBTIMESTAMP
|
A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 3 digits.
|
|
DT_DBTIMESTAMP2
|
A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.
|
|
DT_DBTIMESTAMPOFFSET
|
A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.
Unlike the DT_DBTIMESTAMP and DT_DBTIMESTAMP2 data types, the DT_DBTIMESTAMPOFFSET data type has a time zone offset. This offset specifies the number of hours and minutes that the time is offset from the Coordinated Universal Time (UTC). The time zone offset is used by the system to obtain the local time.
The time zone offset must include a sign, plus or minus, to indicate whether the offset is added or subtracted from the UTC. The valid number of hours offset is between -14 and +14. The sign for the minute offset depends on the sign for the hour offset:
-
If the sign of the hour offset is negative, the minute offset must be negative or zero.
-
If the sign for the hour offset is positive, the minute offset must be positive or zero.
-
If the sign for the hour offset is zero, the minute offset can be any value from negative 0.59 to positive 0.59.
|
|
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 28.
|
|
DT_FILETIME
|
A 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601. The fractional seconds have a maximum scale of 3 digits.
|
|
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.
|