question

manuu avatar image
manuu 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...
ssis2012ssis-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Tim avatar image
Tim answered
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/)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.