Updated March 16, 2023
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:
- 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 –
