asked
Loading Excel files with multiple sheets and each sheet have different no of columns using SSIS
Hi , i have a excel file which have 4 sheets. 1st sheet has 23 columns. 2nd sheet has 19 columns. 3rd and 4th sheet have 16 and 18 columns respectively. sheet 2nd, 3rd and 4th have all the columns which are available in sheet 1. While loading using for each loop container is gives error '[SSIS.Pipeline] Error: "Excel_File" failed validation and returned validation status "VS_NEEDSNEWMETADATA". Because here different no. of columns. How to load this type of excel file? Thanks in advance...
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
Take a look at a blog post I wrote many years ago. I show an example of dropping and creating an excel sheet, you could utilize this same process for multiple sheets within the Excel destination to accomplish your task. [Using an Excel Destination in SSIS](
http://timradney.com/2011/05/17/using-an-excel-destination-in-ssis-with-x64/)
If the file format is going to be consistent and assuming that you want to extract the same set of columns from each, I'd just add four separate connection managers, one for each sheet. You can then run extracts from the four sheets in parallel within your data flow to a single sql / ole db destination, propogating only the columns that you need to the target table.
17 People are following this question.
