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.

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

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

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.

Sorry ArthurZ I meant 666666 instead of 123456 . To add: I just discovered the Expression Tester last week. Great tool I must say.

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 columnChuck

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

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.

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

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.

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

Now next, what is in the Derived Column Transformation?Arthur My Blog

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

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)

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