Foreach Loop Container gives an error after 5 iterations. SSIS

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:

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

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

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. — Thom A, Jan 25 '18 at 16:45
Yep. Looks like half of my sheets have different names. Please, post an answer and I'll accept it. Thank you — Serdia, Jan 25 '18 at 16:51
@Oleg if it always reading from the first sheet, you can use a script task to achieve this, i provided a code in my answer, check it out. — Hadi, Jan 25 '18 at 17:00

score 1 · Accepted Answer · edited Jan 25 '18 at 18:22

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

Will this always give the correct worksheet? I've found that sometimes this might return something with an "_" at the end. PS - Love it that you switched back to C#! — KeithL, Jan 25 '18 at 18:47
It may return hidden or temporary sheets, which are not visible in excel — Hadi, Jan 25 '18 at 19:03

score 1 · Answer 2 · edited Jan 25 '18 at 17:10

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.

Foreach Loop Container gives an error after 5 iterations. SSIS

2 Answers2

Linked