Search code examples
ssisexpressiontrim

SSIS Variable Expression Trim

I got this string pass into a variable called token.

{"reportStatusToken":"NjVmjYWIwODI0MTVhOzMyNzQ7MTQ0NDg0MjQxMTYxNA=="}

But I've been trying to use the expression builder in SSIS to trim that string to just have the token (see next block for end result). But I can't get it to come out correctly. Maybe i used the wrong string function. I have used Trim and replace. both not working correctly.

NjVmjYWIwODI0MTVhOzMyNzQ7MTQ0NDg0MjQxMTYxNA==

After that save it back to the variable so i can use this token to call it out on other packages. this is package level scope, data type string. Any help would be helpful.

Solution

  • In situations like this, I find reversing the string makes it easier.

    {"reportStatusToken":"NjVmjYWIwODI0MTVhOzMyNzQ7MTQ0NDg0MjQxMTYxNA=="}
         1         2         3         4         5         6         7
1234567890123456789012345678901234567890123456789012345678901234567890

}"==ANxYTMxQjM0gDN0QTM7QzNyMzOhVTM0IDOwIWYjmVjN":"nekoTsutatStroper"{

    If the string is reversed, FINDSTRING with a value of 1 will find the first (last) quote {position 2}. We then need to use that position to help us find the next one {position 48}. I create two SSIS variables of type Int32 and use the following formulas

    PositionUltimateQuoteReverse := FINDSTRING(REVERSE(@[User::token]), "\"", 1)
PositionPenultimateQuoteReverse := FINDSTRING(REVERSE(@[User::token]), "\"", @[User::PositionUltimateQuoteReverse])-1

    While I'm at it, I'll use those two values to determine how many characters are between the two quotes. I'm subtracting 2 here as the above positions include the quotes themselves so take one from each end.

    tokenLength := @[User::PositionPenultimateQuoteReverse] - @[User::PositionUltimateQuoteReverse] -2

    The final calculation we need is to determine where to start slicing and dicing. We simply do the math, plus one, to find the position of the penultimate double quote.

    tokenStartPosition := LEN(@[User::token]) - @[User::PositionPenultimateQuoteReverse] +1

    Finally, we use all of these intermediate calculations to substring our way into finding the final token

    SUBSTRING(@[User::token], @[User::tokenStartPosition], @[User::tokenLength])

    Biml

    The business intelligence markup language, biml, provides us the ability to translate one XML dialect into an SSIS package. Simply download BIDS Helper and install it. Right click on an SSIS project and select Add new biml file. Double click on BimlScript.biml and replace the contents with the following. Once done, right click and select generate new SSIS package.

    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="so_33134095">
            <Variables>
                <Variable DataType="String" Name="token">{"reportStatusToken":"NjVmjYWIwODI0MTVhOzMyNzQ7MTQ0NDg0MjQxMTYxNA=="}</Variable>
                <Variable 
                    DataType="Int32" 
                    Name="PositionUltimateQuoteReverse" 
                    EvaluateAsExpression="true"><![CDATA[FINDSTRING(REVERSE(@[User::token]), "\"", 1) ]]></Variable>
                <Variable
                    DataType="Int32"
                    Name="PositionPenultimateQuoteReverse"
                    EvaluateAsExpression="true"><![CDATA[FINDSTRING(REVERSE(@[User::token]), "\"", @[User::PositionUltimateQuoteReverse])-1]]></Variable>

                <Variable
                    DataType="Int32"
                    Name="tokenLength"
                    EvaluateAsExpression="true">@[User::PositionPenultimateQuoteReverse] - @[User::PositionUltimateQuoteReverse] -2</Variable>

                <Variable
                    DataType="Int32"
                    Name="tokenStartPosition"
                    EvaluateAsExpression="true">LEN(@[User::token]) - @[User::PositionPenultimateQuoteReverse] +1</Variable>

                <Variable 
                    DataType="String" 
                    Name="justToken" 
                    EvaluateAsExpression="true">SUBSTRING(@[User::token], @[User::tokenStartPosition], @[User::tokenLength]) </Variable>

            </Variables>
        </Package>
    </Packages>
</Biml>