EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SSIS Tutorial SSIS Data Types
 

SSIS Data Types

Updated March 16, 2023

SSIS Data Types

 

 

Introduction SSIS Data Types

  • SSIS implements its data types while taking data from various sources, operating it, and exporting it to one of many destinations.
  • These SSIS (SQL Server Integration Services) packages are primarily developed to abstract data from multiple sources, convert it, and load it into various destinations.
  • These data types in SSIS are specific to SSIS and are not similar to those supported by the SQL Server database engine or other database systems.
  • The conversions of data types are controlled discreetly. Still, in case anyone hits difficulties, then one is required to arbitrate in one of the various ways to confirm an applicable conversion.

SSIS Data Types Methods

The SSIS data types can be classified into the succeeding categories listed below:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • String: This type supports Unicode and ANSI character strings. Like: DT_STR, DT_WSTR.
  • Numeric: This type supports the formatted numeric values as decimals, currencies, and signed & unsigned integers. Also, SSIS provisions numeric types more than any other type. Like: DT_I4, DT_NUMERIC, DT_CY.
  • Binary: This type provisions image and binary values. Like: DT_IMAGE, DT_BYTES.
  • Date/Time: This type supports several time and date values or both formats. Like: DT_DBDATE, DT_DBTIMESTAMP.
  • Identifier: This type controls GUIDs (Globally Unique Identifiers). Like: DT_GUID
  • Boolean: This type handles the Boolean values. Like: DT_BOOL.
  • Image: This type controls the image inputs. Syntax: DT_IMAGE.

In a package, when the data is entered into a data flow, the source abstracting it will convert it to SSIS data type. In this process, the Numeric data is allotted a numeric type data; the string type data is allotted a character SSIS data type, and dates are assigned a date SSIS data type. In addition, some other data like BLOBS (Binary Large Object Blocks) and GUIDs are allotted suitable SQL Server Integration Services data types. Suppose if data possess a data type that is not changeable to the SSIS data type, then an error arises.

Since SSIS types of data are self-determining from the further systems, every SSIS type can map to a range of the kinds in those systems. For instance, SSIS will not comprise geospatial data types as a user discovers in the SQL server. SSIS applies an image type mapped exactly to the geospatial types as a substitute. But yet, this image type is not restricted to those geospatial types. Also, it is mapped in SSIS to other data types available in the different systems.

When emerging a custom data flow module in Integration Services, we must work continually with data types, replicating data files into and out of the data flow buffers and converting values by choosing the appropriate methods.

The class named PipelineBuffer delivers a sequence of Set methods to replicate data into buffer columns with a consistent series of Get methods to retrieve data from buffer columns. The following list of tables describes the data type present in the initial column and, after that, lists the conforming Set and Get methods in SSIS data types:

Data Types Set Methods Get Method
DT_BOOL SetBoolean GetBoolean
DY_BYTES Set Bytes GetBytes
DT_CY SetDecimal GetDecimal
DT_DATE SETDateTime GetDateTime
DT_DBTIMESTAMP SetDateTime GetDateTime
DT_DECIMAL SetDecimal GetDecimal
DT_I4 SetIbt32 GetInt32
DT_IMAGE AddBlobData or,

SetBlobData

GetBlobData
DT_Numeric Set Decimal GetDecimal

Data Conversion

For illustration, when the data in a table column in the database does not need the full width assigned by the source data type, we may require modifying the data type of that column. Creating every data row in the table as contracted as possible assists in optimizing the performance when moving data since the slighter every row will be, the quicker the data will move from the source to the destination.

SSIS contains a whole collection of numeric data types, which helps to equate the data types narrowly to the scope of the data. We can take an instance if the table column values have an SSIS data type DT_UI8, then they always possess integers between the range 0 and 3000; also, one can alter the data type with DT_UI2. In the same way, any table column with data type DT_CY may meet the needs of the package data via an integer data type instead of changing the data type to data type DT_I4.

We can modify the data type of a table column using the below techniques:

• Implement an expression for implicitly converting data types.
• Implement the cast operator for converting data types.
• Implement Data Conversion alteration for casting the SSIS data type from a distinct data type to a varied one of a table column.
• Implement the Derived Column transformation for creating a replica of a table column with a changed data type than the original table column.

The data types conversion is categorized into two methods mentioned below:

1. Implicit Conversion
2. Explicit Conversion

Now, let us see a quick overview of each method as follows:

1. Implicit Conversion:

This implicit type of conversion is not observable to the end-user. This method will automatically convert the data types from one type to another. For instance, if a string is made to be equated to an integer, then the string provided will be implicitly transformed to the intended type integer before the proceeding of comparison:

SELECT * FROM Table_Name WHERE (String_Col) = (Numeric_Col)

In SSIS, we can apply various methods for this implicit conversion, like:

  • In the Destination component, mapping columns in the table with several data types.
  • From Advanced Editor, you are altering the data type of column.
  • By means of Script Component.

2. Explicit Conversion:

This explicit type of conversion is observable to the end-user. In this method, the conversion is operated using CONVERT, CAST functions, or other tools. For instance,

SELECT CAST({Numeric_Col} AS Varchar (50)) From Table_Name

In SSIS, we can apply various methods for this explicit conversion, like:

  • Via Data Conversion Transformation
  • Via Derived Column Transformation
  • (DT_WSTR, 50) YEAR(GETDATE() )
  • Via a Script Component

Conclusion

  • SSIS implements its data types to perform many operations on the data, such as moving, regulating, and manipulating it before loading it into the target destination.
  • Further, SSIS comprises data types that support several other database systems like DB2, Jet, and Oracle. In addition, SSIS types provision data from CSV (comma-separated values) files, Excel spreadsheets, directory services, text files, and other sources.

Recommended Articles

This is a guide to SSIS Data Types. Here we discuss the Introduction, methods, and Data Conversion with implementation. You may also have a look at the following articles to learn more –

  1. SSIS
  2. Talend vs SSIS
  3. SSIS Interview Questions
  4. Google Now Assistant

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW