Foreach Loop Container gives an error after 5 iter

Question

I created SSIS package that iterates through Excel spreadsheets and load data into SQL. Using ForEach Loop Container.

For some reason after 5 iterations I got an error.

I tried:

1. Set MaximumErrorCount to 0 on Foreach Loop Container
2. ValidateExternaMetadata set to Off on OLE DB Destination

Why it iterates 5 times and after that gives me an error??

Answer 1

Them main issue is

Opening a rowset for "New_Val$A3:C10000" failed. Check that the object exists in the database

it is not related to the Destination, it looks like the New_Val$ sheet is not found in all worksheets

You can add script task before the DataFlow Task to retrieve the first sheet name, a similar logic to the following:

using System;  
using System.Data;
using System.Data.OleDb;    
using Microsoft.SqlServer.Dts.Runtime;  

public class ScriptMain  
{  

    public void Main()  
    { 

        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\myFolder\\excelfile.xlsx;Extended Properties=\"Excel 8.0;HDR=YES\";";

        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            // Get all Sheets in Excel File
            DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            DataRow dr= dtSheet .Rows[0];

            string sheetName = dr["TABLE_NAME"].ToString();

            // You have to assign value to variable (assuming sheetName is created in SSIS)
            Dts.Variables["sheetName"].Value = sheetName ;
        }

        cmd = null;
        conn.Close();
    }
}

In the Excel Source read the sheet name from a variable , from an SQL command that use this variable from an expression

Note that all worksheets must have the same structure, else it will never succeed

Answer 2

As per my comment:

I don't think the iteration number has anything to do with it, the error here is quite clear:

Opening a rowset for "New_Val$A3:C10000" failed. Check that the object exists in the database.

It's telling you that there is no worksheet called New_Val in your document, or the document doesn't exist. If you're using a loop on files, I'd hazard a guess that the 5th file it is processing is the one missing the worksheet.

If your sheets have known worksheet names, and can be derived (for example because of the file's name), I'd suggest different dataflow tasks, which logic pointing to cause the task flow to do to the right one, depending on the file's name.