在ssis派生列中混合整数和字符串 [英] mixing an integer and string in an ssis derived column

问题描述

我有一个问题，在创建一个派生列，我似乎无法弄清楚。这是简单的SQL，但是在SSIS中我似乎无法做到这一点。

基本上是检查大于零的值，并分配一个字符串结果。

示例：

指标= \ + [IND]> 0？CP：+\

应该产生：

如果IND为20.00
或
指标=CP指标=如果IND为0

我似乎不能混合如果/然后用字符串。任何人都有想法？

解决方案

你在正确的轨道上。我相信你正在寻找的表达是

 （DT_STR，2,1252）（（IND> 0）？CP ：）
  

如果IND的值大于零，则使用字符串CP;否则使用空字符串。该表达式将被放置在派生列任务内。

SSIS中的字符串操作将导致Unicode字符串DT_WSTR作为返回类型。因此，我正在使用（DT_STR，2，1252）调用将显式转换为非Unicode字符串类型来包装我们的表达式的整个结果。

如果NULL达到此表达式，则会在输出中导致NULL。这是否可取决于您。如果没有，那么你需要测试它 ISNULL（[IND]），然后使它取决于CP或空字符串。

源查询

我创建了一个包含以下源查询的简单包

  SELECT 20.0 AS IND UNION ALL SELECT 0; 
  

然后，我将其输出输入到派生列，该列使用上面定义的表达式，然后将数据查看器输出的结果来证明结果符合预期。

结果

我的包

Biml

对于随后在家的人，您可以通过
构建此包1.添加Biml文件到您的SSIS项目（安装 BIDS帮助器后），并将以下内容粘贴到生成的文件中（覆盖现有声明）。
2.编辑ConnectionString的值以指向有效的SQL Server安装。
3.右键单击Biml文件并生成SSIS包将导致一个名为的软件包so_20645240

 < Biml xmlns =http://schemas.varigence.com/biml.xsd> 
<连接> 
<！ - 更改下面的ConnectionString。私有者如果不是2012年，数据来源一定 - > 
< OleDbConnection 
 Name =Src
 ConnectionString =Data Source = localhost\dev2012; Initial Catalog = tempdb; Provider = SQLNCLI11.1; Integrated Security = SSPI; Auto Translate =假; 
 /> 
< / Connections> 
<包> 
< Package Name =so_20645240ConstraintMode =Linear> 
<变量> 
< Variable Name =QuerySourceDataType =String> SELECT 20.0 AS IND UNION ALL SELECT 0; < /可变> 
< / Variables> 
<任务> 
< Dataflow Name =DFT Source> 
<变形> 
< OleDbSource Name =OLE_SRC查询ConnectionName =Src
> 
< VariableInput VariableName =User.QuerySource/> 
< / OleDbSource> 
< DerivedColumns Name =DER Expressions> 
<列> 
< Column Name =IndicatorDataType =StringLength =2>（DT_STR，2，1252）（（[IND]& 0）？CP：） < /列> 
< / Columns> 
< / DerivedColumns> 
< / Transformations> 
< / Dataflow> 
< / Tasks> 
< / Package> 
< / Packages> 
< / Biml> 
  

I have an issue in creating a derived column that I can't seem to figure out. It's simple SQL, but in SSIS I can't seem to do it.

Basically it is checking for a value greater than zero, and assigning a string result. The resul needs to be wrapped in a string as well.

Example:

"Indicator=\"" + [IND] > 0 ? "CP" : "" + "\""

Should yield:

Indicator = "CP" if IND is 20.00 or Indicator = "" if IND is 0

I cannot seem to mix the If/Then with the strings. Anyone have an idea?

解决方案

You were on the right track. I believe the expression you were looking for was

(DT_STR,2,1252)((IND > 0) ? "CP" : "")

If the value of IND is greater than zero, then use the string CP; else use empty string. That expression will get placed inside of a Derived Column Task.

String operations in SSIS are going to result in Unicode strings, DT_WSTR, as the return type. Thus I am wrapping the entire results of our expression with an explicit cast back to a non-unicode string type with the (DT_STR, 2, 1252) call.

In the event that NULL reaches this expression, it will result in a NULL in your output. Whether that's desirable is up to you. If it is not, then you'll need to test for it ISNULL([IND]) and then make it take whichever path makes sense CP or empty string.

Source query

I created a simple package with the following source query

SELECT 20.0 AS IND UNION ALL SELECT 0;

I then fed the output of that into a Derived Column which uses the expression defined above and then put a data viewer on the output of that to demonstrate the results are matching the expectation.

Results

Screen capture of my package

Biml

For those following along at home, you can build this package by 1. adding a Biml file to your SSIS project (after installing BIDS Helper) and pasting the following content into the resulting file (overwriting the existing declaration). 2. Edit the value of the ConnectionString to point to a valid SQL Server installation. 3. Right click on the Biml file and generate SSIS package will result in a package called so_20645240

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <!-- Change ConnectionString below. Privider if not 2012, Data Source for certain -->
        <OleDbConnection
            Name="Src"
            ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
            />
    </Connections>
    <Packages>
        <Package Name="so_20645240" ConstraintMode="Linear">
            <Variables>
                <Variable Name="QuerySource" DataType="String">SELECT 20.0 AS IND UNION ALL SELECT 0; </Variable>
            </Variables>
        <Tasks>
            <Dataflow Name="DFT Source">
                <Transformations>
                    <OleDbSource Name="OLE_SRC Query" ConnectionName="Src"
                >
                    <VariableInput VariableName="User.QuerySource" />
                </OleDbSource>
                <DerivedColumns Name="DER Expressions">
                    <Columns>
                        <Column Name="Indicator" DataType="String" Length="2">(DT_STR, 2, 1252)(([IND] &gt; 0) ? "CP" : "")</Column>
                    </Columns>
                </DerivedColumns>
                </Transformations>
            </Dataflow>
        </Tasks>
        </Package>
    </Packages>
</Biml>

这篇关于在ssis派生列中混合整数和字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！