How to Read AlphaNumeric Data Columns from Excel in SSIS Package

What is the use of IMEX Property in SSIS Excel Connection String

IMEX is a Property used in the Excel Connection string property to read the Alphanumeric data from Excel. This property can be used as "IMEX=1" in Connection string as follows :

Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\

EmpSrcAlphaNumeric.xlsx;

Extended Properties="EXCEL 12.0 XML;HDR=YES,IMEX=1";

Example :
Suppose we have the source data in Excel as follows..

Now we will read the Excel data and see how it looks...( without using the IMEX Property in the Excel Connection String )
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\EmpSrcAlphaNumeric.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

The String data in the Emp_Id column ( DT_R8) are reading as NULL.

Now Enable the IMEX Property in the Connection String for Excel Connection.

Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\EmpSrcAlphaNumeric.xlsx;

Extended Properties="EXCEL 12.0 XML;HDR=YES,IMEX=1";

Next Update the Meta Data for the Source Column "Emp_Id" 

Next Run the Package, now it will read the AlphaNumeric Data from Source...

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------