0

Could anyone please tell me about how to set values of c# script SSIS task's variable to package variable? Example: we have table with one column. In sql task we get it to the full result set as object variable. Then we pass this package variable to script task. After this we do some data manipulation:

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

namespace ST_81cd1d2d439843b086cf3eb4d3b22d66.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    #region VSTA generated code
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion



    public void Main()
    {
        OleDbDataAdapter A = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        A.Fill(dt, Dts.Variables["User::i"].Value);

        int i;

        foreach (DataRow row in dt.Rows)
        {
            object[] array = row.ItemArray;
            i = (int)array[0];
            i += 1;
        }

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

How to move out variables of script task to another sql task for insert data to MS SQL target table? I mean should it be new object package variable or new variables for all column of our first object varialbe? How to correctly move full DataSet or all variables' values from c# script task to sql task?

Thank you a lot.

Is there any features for converting object to DataTable?

    public void Main()
    {
        OleDbDataAdapter A = new OleDbDataAdapter();
        DataTable dt = new DataTable();

        A.Fill(dt, Dts.Variables["User::i"].Value);
        A = (DataTable)Dts.Variables["User::o"];

        int i;

        foreach (DataRow row in dt.Rows)
        {
            object[] array = row.ItemArray;
            i = (int)array[0];
            i += 1;
        }

        //Dts.TaskResult = (int)ScriptResults.Success;
    }
Dmitriy Kravchuk
  • 85
  • 1
  • 10
  • Possible duplicate of [How to access ssis package variables inside script component](http://stackoverflow.com/questions/13450289/how-to-access-ssis-package-variables-inside-script-component) – Tab Alleman Feb 10 '16 at 20:57
  • Unfortunately it's not cause there is no information about how to get updated object variable from script task. – Dmitriy Kravchuk Feb 10 '16 at 21:24
  • If it's not a duplicate, then your question is not clear, because this is how I read your question. – Tab Alleman Feb 10 '16 at 21:29
  • What do I need to make it more clear? Add more information, code or pictures? – Dmitriy Kravchuk Feb 10 '16 at 21:35
  • Well first, you ask about a script `component`, but then you ask about a script `task`, which are two different things, so that's not clear. Then the wording of this isn't clear: "So how to move all values of variables inside script task to another component?" – Tab Alleman Feb 10 '16 at 21:41
  • I've updated my qustion. Is it still so bad for understaning? – Dmitriy Kravchuk Feb 11 '16 at 19:16

1 Answers1

1

Any variable that is actually created IN a script task cannot be "exported" to a package variable.

The correct way to do what you want is to start with TWO package variables, one for before the script task and one for after. Since you started with User::i, let's call the second one User::o ("o" for "out").

You would populate User::i as before, and leaving User::o blank, pass BOTH variables to the Script task, but make sure that User::o is a Read/Write variable, and not read-only.

Then in your script task, instead of creating new datatable called dt you would use User::o as the new datatable, populate it from User::i, and perform your manipulations on it.

When the script task finishes, the package variable User::o will contain your desired results.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52