How to map result sets from an execute SQL task using ManagedDTS

Question

I'm trying to generate some SSIS (2008) packages using C# (.Net 4.0) and ManagedDTS (10.0). So far I've managed to generate a package and add some connections and variables, now I'm trying to populate the control flow, the first task I'm trying to add is an "Execute SQL Task" to check if the source table has any rows so I can abort if it doesn't. How I'd do this if I was building the SSIS package manually is to have the Execute SQL task run a query like this:

SELECT RecordExists = CASE WHEN EXISTS (SELECT * FROM [Schema].[TABLE] WHERE [COLUMN] IS NULL) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END

I'd then set the ResultSet property to "Single row" and then map the Result to a variable:

However I can't see how to do this in C#, this is what I've got so far:

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

...

public Package SsisPackage;

...

SsisPackage.Variables.Add("VAR_RecordExists", false, "User", false);
Executable RecordExists = SsisPackage.Executables.Add("STOCK:SQLTask");
TaskHost thRecordExists = RecordExists as TaskHost;
thRecordExists.Properties["Name"].SetValue(thRecordExists, "Do Records Exist?");
thRecordExists.Properties["SqlStatementSource"].SetValue(thRecordExists, "SELECT RecordExists = CASE WHEN EXISTS (SELECT * FROM [Schema].[TABLE] WHERE [COLUMN] IS NULL) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END");
thRecordExists.Properties["Connection"].SetValue(thRecordExists, "Stage");
thRecordExists.Properties["ResultSetType"].SetValue(thRecordExists, ResultSetType.ResultSetType_SingleRow);

My first problem is that the last line errors with the error: The name 'ResultSetType' does not exist in the current context

My second problem is that even if I get this working I have no idea how to actually map the result set to the variable, I've been using the documentation here but it doesn't mention anything about mapping result sets: https://learn.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/adding-tasks-programmatically. I then found this: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.executesqltask.executesqltask.resultsettype.aspx?f=255&MSPPError=-2147217396 which tells me how to set the Result Set Type but results in the above error. It still doesn't mention how to set the result set mapping.

Any help is appreciated.

Answer 1

So I didn't find a way of doing this using the ManagedDTS Framework but I did get it to work.

What I ended up doing was:

• Save the package without the result set mapping.
• Open the package using XmlDocument

I created a helper class to this end and whilst it may not be the most efficient or clean code (I'm not a normally a C# developer) it does seem to do the job:

public class ResultSetBinding
    {
        string ResultName;
        string DtsVariableName;
        string TaskName;

        public ResultSetBinding(string taskName, string resultName, string dtsVariableName)
        {
            TaskName = taskName;
            ResultName = resultName;
            DtsVariableName = dtsVariableName;
        }

        public void AddResultBinding(string filePath)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(filePath);

            XmlElement root = doc.DocumentElement;

            var nsmgr = new XmlNamespaceManager(doc.NameTable);
            nsmgr.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
            nsmgr.AddNamespace("SQLTask", "www.microsoft.com/sqlserver/dts/tasks/sqltask");

            XmlNodeList executableNodes = root.SelectNodes("//DTS:Executable", nsmgr);

            foreach (XmlNode executableNode in executableNodes)
            {
                if (IsExecuteSQLTask(executableNode, nsmgr))
                {
                    if (IsSpecifiedTask(executableNode, nsmgr))
                    {
                        AddResultBindingToNode(doc, executableNode, nsmgr);
                    }
                }
            }
            doc.PreserveWhitespace = true;
            doc.Save(filePath);
        }

        private bool IsExecuteSQLTask(XmlNode executableNode, XmlNamespaceManager nsmgr)
        {
            foreach (XmlAttribute executableAttribute in executableNode.Attributes)
            {
                if (executableAttribute.Name == "DTS:ExecutableType")
                {
                    return executableAttribute.Value.Contains("ExecuteSQLTask");
                }
            }
            return false;
        }

        private bool IsSpecifiedTask(XmlNode executableNode, XmlNamespaceManager nsmgr)
        {
            foreach (XmlNode propertyNode in executableNode.ChildNodes)
            {
                if (propertyNode.Name == "DTS:Property")
                {
                    foreach (XmlAttribute propertyAttribute in propertyNode.Attributes)
                    {
                        if (propertyAttribute.Name == "DTS:Name" && propertyAttribute.Value == "ObjectName" && propertyNode.InnerText == TaskName)
                        {
                            return true;
                        }
                    }
                }
            }
            return false;
        }

        private void AddResultBindingToNode(XmlDocument doc, XmlNode executableNode, XmlNamespaceManager nsmgr)
        {
            foreach (XmlNode objectNode in executableNode.ChildNodes)
            {
                if (objectNode.Name == "DTS:ObjectData")
                {
                    foreach (XmlNode sqlNode in objectNode.ChildNodes)
                    {
                        if (sqlNode.Name == "SQLTask:SqlTaskData")
                        {
                            XmlElement bindingElement = doc.CreateElement("SQLTask:ResultBinding", "www.microsoft.com/sqlserver/dts/tasks/sqltask");

                            bindingElement.SetAttribute("ResultName", "www.microsoft.com/sqlserver/dts/tasks/sqltask", ResultName);
                            bindingElement.SetAttribute("DtsVariableName", "www.microsoft.com/sqlserver/dts/tasks/sqltask", String.Format("User::{0}", DtsVariableName));

                            sqlNode.AppendChild(bindingElement);
                        }
                    }
                }
            }
        }
    }

Which I then set the following class member:

public List<ResultSetBinding> ResultBindings;

Add the result binding when creating the task:

ResultSetBinding ResultBinding = new ResultSetBinding("Do Records Exist?", "RecordExists", "VAR_RecordExists");
ResultBindings.Add(ResultBinding);

And then once saved loop through and add the result bindings:

foreach (ResultSetBinding ResultBinding in ResultBindings)
{
    ResultBinding.AddResultBinding(FilePath);
}