SQL Server Data type conversion is one of the most common tasks in any ETL process. There are several ways of data conversion in SSIS. We have Special “Data Conversion” transformation task or we can use derive column to change data type of a column or we can change the data type while reading it from source i.e. using convert/cast or any similar function where reading from the source.  These are frequently used data conversion methods but the best performing the data type conversion on the Source component.

Conversion on the Source Component
Data conversion can be done the OLE DB Source/Flat File Source/Excel Source component. To do data conversion at the Source component, we need to change the data type of the output columns. To do this, right-click the OLE DB Source component, and then click Show Advanced Editor. In the Input and Output Properties tab, change the data types of the output columns.

1_SQL_Server_Data_Type_Conversion_Options_in_SSIS

2_SQL_Server_Data_Type_Conversion_Options_in_SSIS

In terms of overall performance (elapsed time and CPU utilization), performing the data type conversion in the Source layer is the fasted.

 

Regards

Rakesh Mishra

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook