Post reply

Changing Values

  • February 1, 2008 at 9:07 am

    #120237

    I'm importing data and using lookups to SQL quite well now, but I'm wondering how to go about changing data itself.

    I'm importing from a text file and I need to check if certain columns contain "Ts" and if so change the value to "Ts2" before it gets put into SQL. This is a case sensitive check I need to perform, I can't do this using SQL as our database is case insensitive. Any idea's how to change data or atleast a pointer to the correct component to use for doing this.

    Thanks

  • February 1, 2008 at 11:21 am

    #774356

    Try using the "Derived Column" data flow transformation.

    In the Expression column of the derived column task, use the String Function REPLACE as follows:

    REPLACE( [FieldName] , "Ts", "Ts2")

    This should hopefully do the trick.

  • February 1, 2008 at 1:39 pm

    #774438

    Y, he's right. Derived Column is the way to go.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 1, 2008 at 1:42 pm

    #774439

    I suppose the biggest question is... does that use REPLACE from the SQL Server or as part of the .net

    The server collation being case insensitive and this particular search/replace I need is case sensitive.

    ts will not change

    Ts will become Ts2

    If that makes sense

  • February 1, 2008 at 1:45 pm

    #774442

    At this point you're using .NET functions.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 4, 2008 at 6:24 am

    #774714

    Yes the SSIS data pipeline IS case sensitive. I've run into this same issue when using T-SQL. The derived column task should work just fine.

  • February 6, 2008 at 3:19 am

    #775495

    Thanks guys, that works a treat. 🙂

    I'm guessing you can't replace the same column twice and so have to use 2 derived columns.

    Also trying to convert Y to 1 and N to 0 in another field, but you can only replace the originating column twice. Unless anyone knows a way to use the same output column name twice.

  • February 6, 2008 at 5:37 am

    #775533

    You will probably need 2 derived column definitions for this. Is this something you can do using T-SQL? If so, you could use a case statement.

    I'm assuming you want to go from a char to an integer value? Be carefull with the datatype conversion if you use the derived column method.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply