none
SQL Server Integration Services 2008 - Derived Column Transformation Expression

    Question

  • I am taking data from an excel spreadsheet (Excel Source)

    Then converted the columns to all strings (Data Conversion Transformation)

    Now this is the expression defined in my derived column transformation

    RIGHT((DT_WSTR,18)"000000000000000000" + REPLACE(((DT_WSTR,18)[Copy of SHARES]),".",""),18)

    And the destination is to a Flat File. My layout has been predefined. 


    As an example before I run the package my values for one of my columns are:
    (Note that these values are coming from excel sheet)

    Ex.
    555555.666666

    888888.000000

    After execution:
    000555555666666001
    000000000000888888

    I was hoping to receive:
    000000555555123456
    000000888888000000

    Why do you think I'm getting a 001 appended at the end of the decimal position and secondly why are the zero's being dropped from this value 888888.000000. I prefer to keep the zero's.

    Thanks for all your help.

    Monday, April 09, 2012 8:02 PM

All replies

  • First take your numeric values and do a replace transform   value * 1000000   - that will take care of your decimal issues and your missing zeros.  Converting numbers with decimal points will always yeild odd results if you are expecting a fixed length string. 


    Chuck

    Monday, April 09, 2012 8:13 PM
  • Using my Expression Tester for SQL Server 2008 I get the output as 000000555555666666 and 000000888888000000 respectively.

    And this is what expected, why do you think you would get 000000555555123456?


    Arthur My Blog

    Monday, April 09, 2012 8:18 PM
  • My apologies but I am an SSIS newbie so I am not sure how I should apply your response. I do not see a replace transformation. Am I just editing my current expression and using the replace function? Not really sure what I am suppose to do. 
    Monday, April 09, 2012 9:21 PM
  • Sorry ArthurZ I meant 666666 instead of 123456
    .

    To add: I just discovered the Expression Tester last week. Great tool I must say.

    • Edited by SSIS2011 Monday, April 09, 2012 9:22 PM
    Monday, April 09, 2012 9:21 PM
  • My apologies but I am an SSIS newbie so I am not sure how I should apply your response. I do not see a replace transformation. Am I just editing my current expression and using the replace function? Not really sure what I am suppose to do. 

    In the derived column transform you can either insert a new column or replace the current value of an existing column

    Chuck

    Monday, April 09, 2012 9:45 PM
  • Sorry ArthurZ I meant 666666 instead of 123456
    .

    To add: I just discovered the Expression Tester last week. Great tool I must say.


    I assume then that the expression works, no more issue, correct?

    Arthur My Blog

    Tuesday, April 10, 2012 1:56 PM
  • I am not sure how to write the expression using the replace function. Should I eliminate what I have and just replace it with "REPLACE" function?

    Am I going from this:

    RIGHT((DT_WSTR,18)"000000000000000000" + REPLACE(((DT_WSTR,18)[Copy of SHARES]),".",""),18)

    To this:

    REPLACE(((DT_WSTR,18) [Copy of SHARES]), ?, ? )     *  1000000  (I am not sure what todo with this)

    Do we know of a book or site where I can see many examples as to how to use the functions within SSIS?

    Thanks.


    • Edited by SSIS2011 Tuesday, April 10, 2012 4:32 PM
    Tuesday, April 10, 2012 4:16 PM
  • let's clarify all again, you have converted the values to DT_STRING with the Data Conversion Task, and after applying the expression in the Derived Column Transformation, you get the output above, and I get a different result, the one you have expected to just stress that out.

    If you do not get what want then you did something to make this happen, now we need to get to bottom of it.


    Arthur My Blog

    Tuesday, April 10, 2012 5:31 PM
  • In addition to my Excel Source these are the other task I have to give you a better picture.
    Tuesday, April 10, 2012 7:41 PM
  • Tuesday, April 10, 2012 7:42 PM
  • Now next, what is in the Derived Column Transformation?

    Arthur My Blog

    Tuesday, April 10, 2012 7:43 PM
  • So far so good, now place two DataViewers - one before the DCT and one after to see what you get as input vs the output.

    Arthur My Blog

    Tuesday, April 10, 2012 7:52 PM
  • OK.

    When looking at the Data Viewers

    Before the DCT:

    555555.666666

    888888

    After DCT:

    Same as above

    But when I look at the Flat File which is my destination these are my values

    055555566666600003 (I have no clue why this is happening)
                                                                                                                                                                                                                                                 000000000000888888 (It makes sense as to why there are no zero's, but what should I be doing to get the zero's)

    Tuesday, April 10, 2012 8:03 PM
  • FYI
    Tuesday, April 10, 2012 8:05 PM
  • Just append them. But I am not sure how you go the 0003 mixed in and where / why 8888s were not used?

    Can you just show what hits the FF?

    I am under an impression you ought to delete this file and make it anew. make sure you override it while processing, too.


    Arthur My Blog

    Tuesday, April 10, 2012 8:11 PM
  • Wednesday, April 11, 2012 2:08 PM
  • The Shares field in the excel sheet is a numeric field with 6 decimal positions with a column width of 15.

    I tend to get excel sheets with a bunch of information that needs to be converted to a text file in a specific layout.

    So I was trying to create a package that will do exactly that. The only issue I was having is the SHARES filed as you have seen.

    What I want to take from the spreadsheet:

    -account numbers(General with column width 11),

    -address fields (8 address fields General with 40 column width) ,

    -shares (Numeric filed 15 column width and 6 decimal) and

    -zip code (General column width 10).

    Now When I receive the sheet I usually open it and edit the header to match the fields in my flat file destination and edit the sheet as explained. 


    • Edited by SSIS2011 Wednesday, April 11, 2012 2:27 PM
    Wednesday, April 11, 2012 2:10 PM