mixing an integer and string in an ssis derived column

Question

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?

Answer 1

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>