0
votes

In SSIS script task, how Can I get all package level variables value at run-time using DTS library or any other way, without adding ReadOnlyVariables or ReadWriteVariables. I need latest/updated values.

1

1 Answers

1
votes
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;  

namespace ST_9646389d9def4d9b8d8b53a9ae45fc56
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {


    public void Main()
            {
       RuntimeWrapper.IDTSProject100 p = ((RuntimeWrapper.IDTSProject100)((Package)Dts.Variables["User::AnyReadOnlyVariable"].Parent).Project);
       Project proj = GetProject(p);

      foreach (PackageItem pkgItem in proj.PackageItems)
                    {
                        gdicLogData = new Dictionary<string, string>();

                        Package pkg = pkgItem.Package;
                        Variables varPackageValiables = pkg.Variables;

                        foreach (Variable pkgVar in varPackageValiables)
                        {

                            if (pkgVar.Namespace.ToString() == "User")
                            {

                                Dictionary.Add(pkgVar.Name, pkgVar.Value.ToString());
                            }
                        }


                        Parameters parameters = pkg.Parameters;

                        foreach (Parameter parItem in parameters)
                        {
                            Dictionary.Add(parItem.Name, parItem.Value.ToString());
                        }


                    }

    }

}
}

==================

 private Project GetProject(RuntimeWrapper.IDTSProject100 proj)
    {
        System.Reflection.PropertyInfo pInfo = proj.GetType().GetProperty("Project",
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.NonPublic |
        System.Reflection.BindingFlags.Instance);
        Project prj = (Project)pInfo.GetValue(proj, null);  

        return prj;
    }