SSIS Data Types Cheat Sheet

SSIS Data Types User Handbook

SSIS is a tool of MSBI which is used to perform the integration services such as data migration, by gathering the data from various sources and storing it in a centralized location. It is mainly used to perform ETL operations.
SSIS uses its own set of data types to perform operations on the data. This cheat sheet is a handy reference that you can use to learn and understand the data types used in SSIS. It is helpful for a beginner who wants to learn SSIS and start working on it.

Watch this MSBI Tutorial full course for Beginners video

SSIS Data Types:

SSIS uses its own data types to perform several operations on the data like move, manage and manipulate before loading it into the target destination. SSIS also includes the data types that support many other database systems such as Jet, Oracle, and DB2.

Classification of SSIS Data Types:

• Numeric: The data type which supports numeric values such as currencies, decimals, and signed and unsigned integers

E.g.: DT_I4, DT_CY, DT_NUMERIC, DT_I2

• String: The data types which support ANSI and Unicode character strings

E.g.: DT_WSTR, DT_STR

• Date/Time: The data types supporting data values, time values or both in various formats

E.g.: DT_DBTIMESTAMP, DT_DBDATE

• Binary: The data type which can support binary and image values

E.g.: DT_BYTES, DT_IMAGE

• Boolean: A data type used to handle Boolean values

E.g.: DT_BOOL

• Identifier: A data type which can be used to handle globally unique identifiers (GUIDs)

E.g.: DT_GUID

Components of SSIS Package:

• OLE DB: It is a connection manager which is used to retrieve the data from and extract the data into the database
• Data Flow: It is a task containing the components required to extract, transform or load the data of a product. Basically, it contains components used to perform ETL operation on the data
• OLE DB source: It is used to retrieve the data from the product table in the database. Here the source uses the OLE DB connection manager to connect to the database
• Data Conversion: It is used for Transformation that converts two columns in the data flow
• Derived column: It is used for Transformation that creates a separate column from the concatenated data from the columns in the data flow
• OLE DB Destination: Used to insert data into the table. The destination also uses the OLE DB connection manager to connect to the database

SSIS Date/Time Data Types:

• DT_BOOL: It is a 1-bit Boolean Value
• DT_BYTES:  It is a binary data value and the length is variable, the maximum length is up to 8000 bytes.
• DT_CY: It is a currency value and this data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19 digits
• DT_DATE (Format: yyyy-mm-dd): It is a date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The maximum scale of fractional seconds is 7 digits
• DT_DBDATE: A date structure that consists of year, month, and day.
• DT_DBTIM (Format: hh:mm: ss): A time structure that consists of an hour, minute, and second.
• DT_DBTIME2 (Format: hh:mm: ss[.fffffff]): A time structure that consists of an hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
• DT_DBTIMESTAMP (Format: yyyy-mm-dd hh:mm: ss[. fff]): A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 3 digits
• DT_DBTIMESTAMP2 (Format: yyyy-mm-dd hh:mm: ss[.fffffff]): A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
• DT_DBTIMESTAMPOFFSET (Format: yyyy-mm-dd hh:mm: ss[.fffffff] [{+|-} hh:mm]): A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
• 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 (Format: yyyy-mm-dd hh:mm:ss:fff): A 64-bit value that represents the number of 100-nanosecond intervalThe maximum scale of fractional seconds is 3 digits

Watch this SSIS Tutorial Full Course for Beginners video

• DT_GUID: It is a globally unique identifier (GUID).
• DT_I1: It is a one-byte, signed integer.
• DT_I2: It is a two-byte, signed integer
• DT_I4: It is a four-byte, signed integer.
• DT_I8: It is 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.
• DT_R4: It is a single-precision floating-point value.
• DT_R8: It is a double-precision floating-point value
• DT_STR: It is a null-terminated ANSI/MBCS character string with a maximum length of 8000 characters
• DT_UI1: It is a one-byte, unsigned integer
• DT_UI2: It is a two-byte, unsigned integer
• DT_UI4: It is a four-byte, unsigned integer.
• DT_UI8: It is an eight-byte, unsigned integer.
• DT_WSTR: It is a null-terminated Unicode character string with a maximum length of 4000 characters
• DT_IMAGE: It is a binary value with a maximum size of 2^31 -1 byte
• DT_NTEXT: It is a Unicode character string with a maximum length of 2^30-1 characters
• DT_TEXT: An ANSI character string with a maximum length of 2^31-1 characters

Download a Printable PDF of this Cheat Sheet

With this, we come to an end of the SSIS Data type Cheatsheet. To get in-depth knowledge, check out our interactive, live-online  SSIS Certification Training here, which comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SSIS online training will make you proficient in gathering data from flat files, XML, and relational data sources. It involves working on data extraction, integration, and transformation to align with business needs.