SQLTeam.com | Weblogs | Forums

SSIS Excel Source Metadata Issue


#1

Hi All,

I am receiving Excel (.xlsx) file from the client every day. The problem is I am keep seeing Metadata change problem. I am wondering what is the best way to deal this type of issue. I may wrong, but I think there is a way disable Metadata option. So every time SSIS Package runs it will not compare the metadata with source file it will run fine if Metadata change.
Please advise.

Thank You.


#2

Keep a empty file as template. in a first row you specify the dummy data for each columns with maximum column data length. after running the package remove the dummy first row. this will save you from metadata refresh


#3

Viggneshwar, Thank You for your reply. The problem I am getting when I am reading the file from Excel Source. If I am not going to read the file properly then how I will insert into the template?
Or maybe I am missing something.
Please advise.

Thank You.


#4

Are you receiving the excel with number of columns variance?


#5

Yes, I am.


#6

That is not possible directly as confirmed in the below link
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/71a1a7b6-3245-4298-86e4-227e63c67927/dynamic-column-mapping-in-ssis?forum=sqlintegrationservices

But you can follow some other way to overcome this situation like given in the below link