Hi All,

Here is what i've done to overcome this issue

I took EXECUTE SQL TASK instead of Data Flow task in ForEachLoop Container and used dynamic SQL query that will generate a SQL insert statement as below. In the below query ExcelFileName changes based on the name from ForEach Loop container when it loops through source file folder. In this way there is not issue with the source columns datatypes or length as in Data Flow task. Hope it will be helpful to others.

INSERT INTO dbo.TempTable

(

Col1,Col2,Col3

)

SELECT Col1,Col2,Col3

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=C:\TestFolder\ExcelFileName.xlsx',

'SELECT * FROM [SheetName$]'

)