When data comes in 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 adequate 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 inbetween the Integration Services data types and the managed data types of the Microsoft .NET Framework. For more information about the mapping inbetween 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) .

A Boolean value.

A binary data value. The length is variable and the maximum length is 8000 bytes.

A currency value. This data type is an eight-byte signed integer with a scale of Four and a maximum precision of Nineteen digits.

A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a stationary scale of 7 digits.

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by entire number increments, embarking with 30 December 1899, and midnight as time zero. Hour values are voiced as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values, therefore, there are boundaries on the range of dates that can be introduced in DT_DATE.

On the other forearm, 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 boundaries on ranges of the dates it can present.

A date structure that consists of year, month, and day.

A time structure that consists of hour, minute, and 2nd.

A time structure that consists of hour, minute, 2nd, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.

A timestamp structure that consists of year, month, day, hour, minute, 2nd, and fractional seconds. The fractional seconds have a maximum scale of Three digits.

A timestamp structure that consists of year, month, day, hour, minute, 2nd, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.

A timestamp structure that consists of year, month, day, hour, minute, 2nd, 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 inbetween -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.

An exact numeric value with a immovable precision and a immovable 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.

A 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601. The fractional seconds have a maximum scale of Three digits.

A globally unique identifier (GUID).

A one-byte, signed integer.

A two-byte, signed integer.

A four-byte, signed integer.

An eight-byte, signed integer.

An exact numeric value with a immobilized 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.

A single-precision floating-point value.

A double-precision floating-point value.

A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters. (If a column value contains extra null terminators, the string will be truncated at the occurrence of the very first null.)

A one-byte, unsigned integer.

A two-byte, unsigned integer.

A four-byte, unsigned integer.

An eight-byte, unsigned integer.

A null-terminated Unicode character string with a maximum length of 4000 characters. (If a column value contains extra null terminators, the string will be truncated at the occurrence of the very first null.)

A binary value with a maximum size of Two 31 -1 (Two,147,483,647) bytes. .

A Unicode character string with a maximum length of Two 30 – 1 (1,073,741,823) characters.

An ANSI/MBCS character string with a maximum length of Two 31 -1 (Two,147,483,647) characters.

If the data in a column does not require the utter width allocated by the source data type, you might want to switch the data type of the column. Making each data row as narrow as possible helps optimize spectacle when transferring data because the narrower each row is, the swifter the data is moved from source to destination.

Integration Services includes a finish 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 inbetween 0 and 3000, you can switch 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 switch the data type to DT_I4.

You can switch the data type of a column in the following ways:

Use the cast operator to convert data types. For more information, see Cast (SSIS Expression) .

Use the Data Conversion 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 .

Use the Derived Column transformation to create a copy of a column that has a different data type than the original column. For more information, see Derived Column Transformation .

### Converting Inbetween Strings and Date/Time Data Types

The following table lists the results of casting or converting inbetween date/time data types and strings:

When you use the cast operator or the Data Conversion transformation, the date or time type data type will be converted to the corresponding string format. For example, the DT_DBTIME data type will be converted to a string that has the format, “hh:mm:ss”.

When you want to convert from a string to a date or time data type, the string must use the string format that corresponds to the adequate date or time data type. For example, to successfully convert some date strings to the DT_DBDATE data type, these date strings must be in the format, “yyyy-mm-dd”.

yyyy-mm-dd hh:mm:ss[.fffffff] [ <+|->hh:mm]

In the format for DT_FILETIME and DT_DBTIMESTAMP fff is a value inbetween 0 and 999 that represents fractional seconds.

In the date format for DT_DBTIMESTAMP2, DT_DBTIME2, and DT_DBTIMESTAMPOFFSET, fffffff is a value inbetween 0 and 9999999 that represents fractional seconds.

The date format for DT_DBTIMESTAMPOFFSET also includes a time zone element. There is a space inbetween the time element and the time zone element.

### Converting Date/Time Data Types

You can switch the data type on a column with date/time data to extract the date or the time part of the data. The following tables list the results of switching from one date/time data type to another date/time data type.

#### Converting from DT_FILETIME

Convert DT_FILETIME to

Converts the data type.

Liquidates the time value.

Eliminates the date value.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional digits that the DT_DBTIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Liquidates the date value represented by the DT_FILETIME data type.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Converts the data type.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the time zone field in the DT_DBTIMESTAMPOFFSET data type to zero.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMPOFFSET data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

#### Converting from DT_DATE

Convert DT_DATE to

Converts the data type.

Liquidates the time value represented by the DT_DATA data type.

Eliminates the date value represented by the DT_DATE data type.

Eliminates the date value represented by the DT_DATE data type.

Converts the data type.

Converts the data type.

Sets the time zone field in the DT_DBTIMESTAMPOFFSET data type to zero.

#### Converting from DT_DBDATE

Convert DT_DBDATE to

Sets the time fields in the DT_FILETIME data type to zero.

Sets the time fields in the DT_DATE data type to zero.

Sets the time fields in the DT_DBTIME data type to zero.

Sets the time fields in the DT_DBTIME2 data type to zero.

Sets the time fields in the DT_DBTIMESTAMP data type to zero.

Sets the time fields in the DT_DBTIMESTAMP data type to zero.

Sets the time fields and the time zone field in the DT_DBTIMESTAMPOFFSET data type to zero.

#### Converting from DT_DBTIME

Convert DT_DBTIME to

Sets the date field in the DT_FILETIME data type to the current date.

Sets the date field in the DT_DATE data type to the current date.

Sets the date field in the DT_DBDATE data type to the current date.

Converts the data type.

Sets the date field in the DT_DBTIMESTAMP data type to the current date.

Sets the date field in the DT_DBTIMESTAMP2 data type to the current date.

Sets the date field and the time zone field in the DT_DBTIMESTAMPOFFSET data type to the current date and to zero, respectively.

#### Converting from DT_DBTIME2

Convert DT_DBTIME2 to

Sets the date field in the DT_FILETIME data type to the current date.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_FILETIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the date field of the DT_DATE data type to the current date.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DATE data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the date field of the DT_DBDATE data type to the current date.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the destination DT_DBTIME2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Set the date field in the DT_DBTIMESTAMP data type to the current date.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the date field in the DT_DBTIMESTAMP2 data type to the current date.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the date field and the time zone field in the DT_DBTIMESTAMPOFFSET data type to the current date and to zero, respectively.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMPOFFSET data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

#### Converting from DT_DBTIMESTAMP

Convert DT_DBTIMESTAMP to

Converts the data type.

If a value represented by the DT_DBTIMESTAMP data type overflows the range of the DT_DATE data type, comebacks the DB_E_DATAOVERFLOW error. For more information, see Error Treating in Data .

Eliminates the time value represented by the DT_DBTIMESTAMP data type.

Liquidates the date value represented by the DT_DBTIMESTAMP data type.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Eliminates the date value represented by the DT_DBTIMESTAMP data type.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the time zone field in the DT_DBTIMESTAMPOFFSET data type to zero.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMPOFFSET data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

#### Converting from DT_DBTIMESTAMP2

Convert DT_DBTIMESTAMP2 to

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_FILETIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

If a value represented by the DT_DBTIMESTAMP2 data type overflows the range of the DT_DATE data type, the DB_E_DATAOVERFLOW error is returned. For more information, see Error Treating in Data .

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DATE data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Liquidates the time value represented by the DT_DBTIMESTAMP2 data type.

Eliminates the date value represented by the DT_DBTIMESTAMP2 data type.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Eliminates the date value represented by the DT_DBTIMESTAMP2 data type.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

If a value represented by the DT_DBTIMESTAMP2 data type overflows the range of the DT_DBTIMESTAMP data type, comes back the DB_E_DATAOVERFLOW error.

DT_DBTIMESTAMP2 maps to a SQL Server data type, datetime2, with a range of January 1, 1A.D. through December 31, 9999. DT_DBTIMESTAMP maps to a SQL Server data type, datetime, with smaller a range of January 1, 1753 through December 31, 9999.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP data type can contain. After removing the fractional 2nd value, generates a report about this data truncation.

For more information about errors, see Error Treating in Data .

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the destination DT_DBTIMESTAMP2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Sets the time zone field in the DT_DBTIMESTAMPOFFSET data type to zero.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMPOFFSET data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

#### Converting from DT_DBTIMESTAMPOFFSET

Convert DT_DBTIMESTAMPOFFSET to

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to Coordinated Universal Time (UTC).

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_FILETIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC.

If a value represented by the DT_DBTIMESTAMPOFFSET data type overflows the range of the DT_DATE data type, comes back the DB_E_DATAOVERFLOW error.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DATE data type can contain. After removing the fractional 2nd value, generates a report about this data truncation.

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC, which can affect the date value. The time value is then eliminated.

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC.

Liquidates the data value represented by the DT_DBTIMESTAMPEOFFSET data type.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC.

Eliminates the date value represented by the DT_DBTIMESTAMPOFFSET data type.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIME2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC.

If a value represented by the DT_DBTIMESTAMPOFFSET data type overflows the range of the DT_DBTIMESTAMP data type, the DB_E_DATAOVERFLOW error is returned.

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP data type can contain. After removing the fractional 2nd value, generates a report about this data truncation.

Switches the time value represented by the DT_DBTIMESTAMPOFFSET data type to UTC.

Liquidates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the DT_DBTIMESTAMP2 data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

Eliminates the fractional 2nd value when its scale is greater than the number of fractional 2nd digits that the destination DT_DBTIMESTAMPOFFSET data type can contain. After removing the fractional 2nd value, generates a report about this data truncation. For more information, see Error Treating in Data .

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 about these mapping files, see SQL Server Import and Export Wizard .

These mappings are not intended to represent rigorous 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.

You can use the SQL Server data types to estimate the size of corresponding Integration Services date and time data types.

SQL Server (SqlClient)

binary, varbinary, timestamp

binary, varbinary, timestamp

TIMESTAMP, DATE, INTERVAL

TIME, TIMESTAMP, DATE

TIME, TIMESTAMP, DATE

nchar, nvarchar, sql_variant, xml

char, varchar, nchar, nvarchar, sql_variant, xml

CHAR, ROWID, VARCHAR2, NVARCHAR2, NCHAR

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

LONG, CLOB, NCLOB, NVARCHAR, TEXT

LONG VARCHAR, NCHAR, NVARCHAR, TEXT

LONG VARCHAR, DBCLOB, NCHAR, NVARCHAR, TEXT

LONG VARCHAR FOR BIT DATA

LONG VARCHAR FOR BIT DATA, CLOB

For information on mapping data types in the data flow, see Working with Data Types in the Data Flow .

For the latest downloads, articles, samples, and movies from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.