bing
Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up
×
UPTO
50%
OFF!
Intellipaat
Intellipaat

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 which 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

Learn for free ! Subscribe to our youtube Channel.

You can also download the printable PDF of this SSIS Data types cheat sheet.

ssis data types PPT

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 supports 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 supports 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 manger 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 to 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, 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 hour, minute, and second.
  • DT_DBTIME2 (Format: hh:mm: ss[.fffffff]): A time structure that consists of 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 a12-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
  • 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 bytes
  • 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 SSIS Data type Cheat sheet. To get in-depth knowledge, check out our interactive, live-online  SSIS certification Training here, that 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

Previous

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on SSIS Data Types Cheat Sheet"

    Leave a Message

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.
    top

    Sales Offer

    Sign Up or Login to view the Free SSIS Data Types Cheat Sheet.