1

Here's what I'm trying to do:

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.

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.

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:

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

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.

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.

What is going wrong?

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
Molenpad
  • 833
  • 2
  • 14
  • 34
  • Can you put a breakpoint on the Script Task and see which code line is breaking? You might get more details of the error when you debug. – Martin Jan 23 '16 at 09:13
  • I'll do that and report back. Thanks =) – Molenpad Jan 23 '16 at 12:45
  • My code errors on the OLEda.Fill function with following error: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. – Molenpad Jan 23 '16 at 13:05
  • OK now you need to split that line out. Put `String z = Dts.Variables["User::TheRecords"].Value.ToString()` on the line before and see if it errors out on that line. When you set up you task did you add `TheRecords` to your read only variables collection? Also keep in mind I don't think your prior loop is appending to your recordset - I'm guessing it initialises it and loads it each time so you'll only get the value of the last loop.... I think. – Nick.Mc Jan 23 '16 at 14:16

1 Answers1

2

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

Martin
  • 644
  • 5
  • 11