SSIS passing custom objects

Question

I've created a custom class in one of my flow tasks and assign values to it's properties. I store the collection of these custom classes in the Object variable Later on in a different script task i want to read the values from this collection of custom objects.

The custom class is unknown in the other ssis components. I can't create a dll and store it on the SQL server so how to a transport the collection of custom objects.

I can get them to the script tasks and they have all the properties and correct values but there doesn't appear to be a way to accesss the properties. I duplicated the custom class and tried casting it but SSIS knows it's not the same one and won't play ball.

how do i access this data?

Erick

Answer 1

First, if you want to properly use a custom class library in your SSIS project (to be able to use it across the whole package), you have to sign your assembly with the strong name. And add it to GAC. I don't know any other way. And then you can reference it from your project.

Another thing is you have to use the properly scoped variable to stuff your object diagram somewhere. Then you are able to retreive it later, e.g. in some other script task.

HTH

P.S. see this, in the end it says where to put the dll so maybe you don't have to sign it after all (?!?)

Answer 2

I would recommend to store your data in DataSet object - this way you can always access your data in other script task / components. So e.g. in your first script task store your data with

...
DataSet dataSet = new DataSet("Data");
DataTable table = dataSet.Tables.Add("Table");
Dts.Variables["VariableOfTypeObject"].Value = dataSet;
...

and in the other script task access it via

Dataset myData = Dts.Variables{"VariableOfTypeObject"].Value as DataSet;

Alternatively, if you are looking for a native way to store data in a SSIS variable of type object within a dataflow you can use the "Recordset Destination".

Answer 3

If in fact you cannot create either a custom DLL or a custom SSIS component, your only alternative will be to use .NET reflection in your consuming scripts to find the appropriate methods/properties and access them dynamically.

That will be a significant amount of work, and the programming environment offered by SSIS isn't really conducive to it. If you really cannot deploy non-package code to your servers, I'd seriously rethink the architecture that needs that custom class.

EDIT: Simple access wasn't as hard as I thought it might be. Assuming you have a package-level variable of type Object named "SomeObject", you could build a control flow like this:

The code for SCR_SetVariables is:

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

namespace ST_00e1230a50e6470e8324a14e9d36f6c7.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
        class SomeClass
        {
            public string Greeting { get; set; }
            public override string ToString()
            {
                return String.Format("My greeting is {0}", this.Greeting);
            }
        }
        public void Main()
        {
            SomeClass myClass = new SomeClass();
            myClass.Greeting = "Hello, world!";
            Dts.Variables["SomeObject"].Value = myClass;
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

and the code for SCR_ShowVariables is:

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

namespace ST_eea68a39bda44e9d9afaa07d2e48fc0f.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()
        {
            object someObject = Dts.Variables["SomeObject"].Value;
            PropertyInfo getGreeting = someObject.GetType().GetProperty("Greeting");
            string greeting = (string)getGreeting.GetValue(someObject, null);
            string msg = String.Format("someObject.Greeting = '{0}'", greeting);
            System.Windows.Forms.MessageBox.Show(msg);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

This will show the following messagebox:

Answer 4

Alternatively, you can create a .Net library project, added it to the GAC and reference it in your scripts if you don't what to go the route of reflection.