如何在ssis脚本任务中显示变量 [英] How to display variable in ssis script task

问题描述

我有一个执行 SQL 任务"，它产生一个行数.见屏幕 1.

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

我正在尝试在 ssis 的脚本任务"中打印ContactRowCount".参见屏幕 2.

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

但 Dts.Variables["User::ContactRowCount"] 为空.我可以取回它.任何人都知道如何从脚本任务中的执行 SQL 任务"中检索变量值

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

屏幕 - 1

屏幕 - 2

推荐答案

请阅读文档，所有这些都已涵盖.

Do read documentation, all of this has been covered.

我有两个变量.一个用于我的 SQL，称为 Quqery，它是可选的.另一个是一个名为 RowCount 的 Int32.

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

我有一个使用 OLE DB 连接管理器的执行 SQL 任务.我已将其指定为单行的 ResultSet.我使用我的 Query 变量作为源.

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.

值为

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

在结果集选项卡中，我将 0 ResultSet 映射到我的变量 User::RowCount.

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

如果您使用不同的连接管理器提供程序(ADO.NET 或 ODBC)，那么这些语义都会发生变化.但它被记录在案.

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

我确保我将变量作为只读对象传递

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

在我的脚本中，我需要访问该变量.这是区分大小写的.此外，我想要 .Value 属性.您的代码，如果它可以工作，会将 SSIS 变量转换为字符串.这导致对象默认发出其名称 Microsoft.SqlServer.Dts.Runtime.Variable

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

相反，我们希望访问作为对象返回的 .Value 属性.如果你想用这个值做一些数学运算，那么你需要把它转换成一个整数值，但因为我们要字符串，所以很容易.

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;
        }
    }
}

这篇关于如何在ssis脚本任务中显示变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！