SSIS 脚本任务和 SQL 批量插入的问题 - C# [英] Issues with SSIS Script Task and SQL Bulk Insert - C#

问题描述

这是我想要做的:

我正在使用执行 SQL 任务(在 ForEach 循环容器中)循环遍历大约 20 个具有相同查询的 SQL Server 实例，每个实例显然返回具有相同列的行.这将转到具有正确名称 (0) 和变量名称 User::TheResults.

I'm using an Execute SQL Task (in a ForEach loop container) to loop through around 20 SQL Server instances with the same query, each returning a row with the same columns obviously. This is going to a Full Result Set with the correct name (0) and variable name User::TheResults.

这一点似乎工作正常，即没有错误.

This bit seems to working ok, i.e. it's not erroring.

然后我使用脚本任务 (C#) 用 SSIS 结果集填充数据表，并将其批量复制到预先存在的 SQL Server 表中.

I'm then using a Script Task (C#) to populate a DataTable with the SSIS Result Set and Bulk Copy it into a pre-existing SQL Server table.

这是 C# 代码...

Here's the C# code...

    public void Main()
    {
        // TODO: Add your code here

        OleDbDataAdapter oleDA = new OleDbDataAdapter();
        DataTable dt = new DataTable();

        oleDA.Fill(dt, Dts.Variables["User::TheRecords"].Value.ToString());

        SqlConnection connection = new SqlConnection("Data Source=Server1\Instance1;Initial Catalog=iteration_test;Integrated Security=SSPI");

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "dbo.versiontest";

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }

我在执行任务时收到错误消息，但它不够明确，无法指向特定问题.它只是说明:

I'm getting an error when the task is executed, but it's not explicit enough to point to a particular problem. It simply states:

在 System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)在 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)在 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)在 System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] 修饰符, CultureInfoculture, String[] namedParams)在 Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

我是一名 DBA，目前参与了很多 BI 工作，我的 C# 技能一般.我假设问题出在我的代码逻辑内.

I am a DBA who is currently getting involved in a lot of BI work, and my C# skills are so-so. I'm assuming the issue is within my code logic.

简单回顾一下，我在 SSIS 中设置了一个结果集作为变量，我想将其设置为 SQL Server 表.我在 SSIS 中的脚本任务中使用上述脚本执行此操作.

So just to recap, I have a Result Set in SSIS set as a variable, which I want to get to a SQL Server table. I'm doing that using the above script inside a Script Task in SSIS.

出了什么问题?

推荐答案

从错误来看，脚本任务似乎无法识别您的变量.要解决此问题，请将变量 User::TheRecords 添加到脚本任务中的 ReadOnlyVariables 或 ReadWriteVariables 集合.请参阅此 图片 供参考

From the error it looks like Script Task is not recognizing your variable. To fix this add your variable User::TheRecords to the ReadOnlyVariables or ReadWriteVariables collection in the Script Task. See this image for a reference

这篇关于SSIS 脚本任务和 SQL 批量插入的问题 - C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！