Use SSIS Variable in another class besides ScriptM

2019-07-02 01:47发布

I have a C# script task in SSIS that I can pass a variable to no problem. I have another class that I created in the script call otherclass.cs. How do I use the variable within otherclass.cs?

I tried to just do this:

_urlBase = Dts.Variables["User::URLBase"].Value.ToString();

But I get this error:

The name 'Dts' does not exist in the current context

I'm pretty new to C# and classes so I may not be asking the question correctly. Is what I'm asking even possible?

Thanks.


SOLUTION

Here is my solution to what I was trying to accomplish:

In ScriptMain.cs created new service like this:

OtherService ws = new OtherService(Dts.Variables["User::URLBase"].Value.ToString());

In the OtherService.cs I had this:

class OtherService
{
    public OtherService(string urlBase)
    {
        _urlBase = urlBase;
        //do other stuff
    }
    //other methods and stuff
}

标签: c# ssis
1条回答
甜甜的少女心
2楼-- · 2019-07-02 02:18

When you add a C# Script Task in BIDS 2008, notice this line in the ScriptMain task that it generates for you:

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

This says that ScriptMain is a subclass of Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase. That base class (VSTARTScriptObjectModelBase) defines a member Dts of type Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel. This in turn has a member named Variables. That is what you are accessing when you type "Dts.Variables" in ScriptMain.

The class you define in "otherclass.cs" is not a subclass of VSTARTScriptObjectModelBase and therefore does not inherit its member Dts. That is why the compiler tells you "The name 'Dts' does not exist in the current context."

If otherclass needs access to a variable's value then as cfrag suggests you can pass this value in when you instantiate a member of your class or when you call the class member function that needs this value. If you will need to access multiple variables, consider something like this:

public otherclass(Microsoft.SqlServer.Dts.Runtime.Variables dtsVariables)
{
    DtsVariables = dtsVariables;
    // other stuff
}
private Microsoft.SqlServer.Dts.Runtime.Variables DtsVariables;

Now otherclass has an instance member named DtsVariables which its non-static members can use to access the Variables collection passed in when the object was created. You would instantiate it and call methods from ScriptMain like this:

otherclass oc = new otherclass(Dts.Variables);
string url = oc.MakeURL();

Inside otherclass.MakeURL(), you can use the instance variable DtsVariables like you would have used Dts.Variables in ScriptMain, e.g.:

public string MakeURL()
{
    return DtsVariables["User::URLBase"].Value.ToString() + "/default.aspx";
}
查看更多
登录 后发表回答