Friday, November 17, 2017

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 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog