在派生列转换SSIS中使用Variable作为表达式 [英] Using Variable as expression in Derived column transformation SSIS

问题描述

本质上，我是一个带有执行SQL语句的SSIS pkg，它基于某些表值动态写入一个REPLACE函数。 （即REPLACE（REPLACE（Col1， * ，，），@@@，）

Essentially I a SSIS pkg with an Execute SQL statement that dynamically writes a REPLACE function based on some table values. (ie REPLACE(REPLACE(Col1,"*","",),"@@@","")

ExecuteSQL结果被放在变量@Cleanse

ExecuteSQL result is put to variable @Cleanse

在我的派生列转换中，我试图调用@User :: Cleanse作为一个表达式来从DataFlow替换Col1的值。

In my Derived Column conversion Im trying to call @User::Cleanse as an expression in to replace the Value of the Col1 from the DataFlow.

结果似乎是拉取@Cleanse的结果，并将其用作字符串值，而不是将其应用为REPLACE函数。

The result appears to be pulling the result of @Cleanse and using it as a string value rather than applying it as the REPLACE function.

当调试@Cleanse值似乎将\放在字符串中时，我不认为应该重要，因为它似乎正在将其应用于其他值而没有问题

When Debugging the @Cleanse value appear to be putting \ in the string, which I dont think should matter as it seems to be appliying this to other values without a problem

结果是当用完了CSV时，这就是把

The result is that when running out to the CSV this is putting

REPLACE（COLLACE（COL1）in Col1

"REPLACE(REPLACE(Col1" in Col1

* 在Col2中

）

如何获取派生列转换，将变量看作函数，而不是字符串值？

How can I get the Derived column transformation to 'see' the variable as the function, not a string value?

非常感谢提前

Many thanks in advance

推荐答案

将变量的 EvaluateAsExpression 属性设置为

Set the EvaluateAsExpression property of the variable to True.

然而，使用变量映射执行SQL任务的窗格可能是一个更好的解决方案

However, binding variables as parameters to an SQL query using the Variable Mappings pane of the Execute SQL task might be a better solution

这篇关于在派生列转换SSIS中使用Variable作为表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！