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.
In terms of overall performance (elapsed time and CPU utilization), performing the data type conversion in the Source layer is the fasted.