How to read mixed data type from excel file in SSIS

To fix the issue where SSIS was reading mixed data  type wrong from an EXCEL file I had to change the registry information
I had a file where the source file had account numbers where some rows had value as integer and some had alpha numeric character. When the account number had first few rows as integer the remaining rows with the alphanumeric account number value was not getting read and the value was getting imported as NULL. I followed the following steps to fix this issue in EXCEL 2012 .

1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 14.0 ► Access Connectivity Engine ► Engines.
3. Double-click the Excel node.
4. In the right panel, double-click the TypeGuessRows entry.
5. Change the value data from 8 to 0.
6. Click OK.
7. Select File ► Exit to exit the Registry Editor window.

This modification instructs Excel to scan all of the rows in the table for their data types. This action might decrease performance on very large tables, but the importation of data will be more accurate. You can also set TypeGuessRows from 0 to 16, inclusive, where 0 scans all of the rows and the other numbers set the number of rows to scan first.

Creating Excel File using SSIS

This process creates a excel file with data from the database.

In Execute SQL Task we create the table( excel sheet) . The columns are the column names returned by the query and the names appear as it is in the header of the sheet if we have checked the box where is says first row contains header.

 The following is the data flow task where the excel destination uses a dynamic file connection manager.

 

We are creating the ExcelFilepath property of the connection manager as follows. WE declare a user variable called @varExcelFileName and build the expression for it as follows:

@varExcelFileName  =

@[User::varOutFilePath]+ Replace(@[User::varReconFileName],".xls","") + "_" + (DT_WSTR, 4) YEAR( GETDATE()  ) +(DT_WSTR, 2) MONTH( GETDATE()  )+(DT_WSTR, 2) DAY( GETDATE()  ) + ".xls"