I have a 'Execute SQL task' which product a row count. see screen 1.

I am trying to print 'ContactRowCount' in ssis 'Script task'. See screen 2.

But Dts.Variables["User::ContactRowCount"] is null. I can retrieve it. Anyone knows how to retrieve variable value from 'Execute SQL task' in script task

Screen - 1

Screen - 2

回答1:

Do read documentation, all of this has been covered.

Variables

I have two variables. One is for my SQL, called Quqery, which is optional. The other is an Int32 called RowCount.

Execute SQL Task

I have an Execute SQL task that uses an OLE DB Connection Manager. I have specified that it as a ResultSet of Single Row. I use my Query variable as the source.

The value is

SELECT COUNT(1) AS ContactRowCount FROM sys.columns AS SC;

In the Result Set tab, I map the 0 ResultSet to my variable User::RowCount.

If you are using a different Connection Manager provider (ADO.NET or ODBC), then these semantics all change. But it's documented.

Script Task

I ensure that I am passing in my variable as a read only object

Within my script, I need to access that variable. This is case sensitive. Furthermore, I want the .Value property. Your code, were it to work, would be casting the SSIS Variable to a string. This results in the default of the object emitting its name Microsoft.SqlServer.Dts.Runtime.Variable

Instead, we will want to access the .Value property which is returned as an object. If you were trying to do something mathematical with this value, then you'd need to convert it to an integer value but since we're going to string, that's easy.

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

namespace ST_454527867e9d448aad6a7f03563175b2.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()
        {
            string strMessage = Dts.Variables["User::RowCount"].Value.ToString();
            MessageBox.Show(strMessage);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

回答2:

use variable name in the script task and not the result set name.