New to SSIS and dts. Stumbling along on this one, really looking for resources and help. I have a flat file, i defined through connection manager and (for now) a fixed destination excelfile I defined in connection manager. My dataflow, is pretty simple, mapping two fields to each other an amount field and a phone field in a flat file source and excel destinatinon. the amount column is formated as a number in the excel, and a currency in the connection and both input output properies. A few questions, 1. why do cells on the excel show up with that green wedge on the upper left? appears to be a formating issue. 2. in the flat file, my amount field does not have the decimal, what would be the best way to apply that? it's should be implied. 3.Everytime I test the SSIS package, it keeps appending to the excel (it actually does not even work right on the second run).What's the best way to have it write to a fresh file? have an ssis script task copy the file from an empty template? 4. Id like to remove the last row? what's the best way to do that? Thanks for any help or information!

1 I think that is somthing in the Excel side; nothing you can can change within SSIS. A work around may be to have an empty excel template with the required formating that SSIS uses as target? 2. I don't understand that one 3. You may have more thatn one work around here: Have an empty template that you copy every time the package runs; that way you asure the excel file is empty each time. Create the excel file at run time via execute sql task before the data flow is executed. I have an example here: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html 4. Not sure about the best; but you can count the number of rows in the flat file first in a first dataflow; then in a secon data flow, use a conditional split to discard the last row. This thread has more details: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1178578&SiteID=1&PageID=1

On number 2, is the scenario that flat file has an implied decimal place? That is, the number appears in the flat file as 12399, which represents 123.99? If so, use a derived column transform to either divide the number by 100, or multiply by .01.

To get excel to understand/accept the expected format, Is there no way to send formating information along with the data to excel. I had to do this for a custom exel export control I found but it was used under asp.net Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>" sw.Write(strStyle) thanks.

I am facing same problem I am also getting data from sql server and exporting to excel desination with the help of SSIS and my data in excel comming with green arrow at the left hand side with all numeric data. I do not know how to rid off that green arrow though I tried different way to do so. I used Data conversion prior to excel but nothing worked. So please suggest me a batter way to get all numbers in excel files. I even tried formating my teplates as numeric but did not work. thank you in advance