5次迭代后，Foreach循环容器给出错误. SSIS [英] Foreach Loop Container gives an error after 5 iterations. SSIS

问题描述

我创建了SSIS程序包，该程序遍历Excel电子表格并将数据加载到SQL中. 使用ForEach循环容器.

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

由于5次迭代后的某种原因，我得到了一个错误.

For some reason after 5 iterations I got an error.

我尝试过:

1. 在Foreach循环容器上将MaximumErrorCount设置为0
2. ValidateExternaMetadata在OLE DB Destination上设置为Off

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

为什么要迭代5次，然后又给我一个错误?

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

推荐答案

主要问题是

打开"New_Val $ A3:C10000"的行集失败.检查对象是否存在于数据库中

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

它与目标无关，似乎在所有工作表中都找不到New_Val$工作表

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

您可以在DataFlow Task之前添加脚本任务以检索第一个工作表名称，其逻辑类似于以下内容:

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();
    }
}

在Excel Source中，从变量，从使用表达式中使用该变量的SQL命令中读取工作表名称

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

请注意，所有工作表必须具有相同的结构，否则它将永远不会成功

这篇关于5次迭代后，Foreach循环容器给出错误. SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！