SQL Server Performance Forum – Threads Archive

export to excel – formatting number output

Hi all, Once again, SSIS is giving me a ‘F.U.N.’ time (ask for definition of the F.U.N. acronym another time <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ). I have a relatively simple task – create an excel spreadsheet with 3 columns of data – Id, Description and Sales. ID and Description are text, sales is int. So my SP aggregates and creates my resultset in my OLE DB Source in the Data Flow. It proceeds to the Excel destination, and that all seems fine. My issue is that the data is being written as text. Looking at the excel destination in Advanced editor: the Excel Destination Input, Input columns are formatted as I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and DT_I4 for the Sales. Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255. The target excel spreadsheet is being created from a template file. That template file has header columns. The target column for the Sales has the entire column formatted to NUMBER (0 decimals). Yet to now avail. When I check the spreadsheet, the column has retained the cell formatting, and I have a ‘I’ pop-up to inform me that ‘someone’ has inserted text data into the number column (even though the data IS number). Since the SP spits out INT, it isn’t a case of receiving a text value, imho. While trying to change the external column data type in the advanced editor, SSIS is quite happy to let me change the value for the Sales output to DT_I4, apply, and ok. Then, when I open it immedaitely aftgerwards, it has reverted to the DT_WSTR’s! AArrgh. If is can’t handle it, at least tell me when I try and change it. don’t let me change it, and then revert back without telling me! Grumble grumble… So – anyone know a way around this? Panic, Chaos, Disorder … my work here is done –unknown

THis is not a solution, but have you tried the same with DTS?> Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

erm – no – I haven’t tried DTS.
Can I deploy DTS jobs to a SQL 2005 server – I guess it probably would work… although (strange to admit), I’ve probably (in fact defintely) got more experience with SSIS (all of 2 months :-0 ) than DTS, even though I’ve been using SQL2000 for 5 years or so – I’ve just never needed to use it, really. Do you think/know that the same problem doesn’t exist in SQL2000 DTS? At the end of the day, it won’t really be a problem, since the data is being generate for human review – it isn’t critical that it be in number format in the generated spreadsheet, but it irritates me that it isn’t, and it feels like something we should be able to do – agree? Panic, Chaos, Disorder … my work here is done –unknown

quote:Originally posted by satya THis is not a solution, but have you tried the same with DTS?> Well i see some really stupied answers. But not with a solution. I guess with that kind of answers i could also say that i really like the sun and the beach. Anyway i have that same problem with the output in excel. It is really not clear how to implement a good solution. I have the problem with a csv file that i would like to translate to an xls file thru a dts package. Offcourse i want it to be fully automated. I can create an excel file and i can format the excel file with datafilters, bold, autofit whatever and i can also create the format of the destination cells in excel. The thing is that the DTS package in the datapump section just thinks that every output cell is in the varchar 255 format. I have seen in the datapump properties that the destination cells are all of the same type. I tried to change the properties with a dynamic properties object that would change the properties at runtime. But nothing seems to help all the output cells (columns) are still in the varchar(255) fromat.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

http://www.acronymattic.com/results.aspx?q=F.U.N. Let me guess… It’s the last one in that list? If not even worse. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

For those of you with that evil grin, FUN – Function Unknown Now [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Roji. P. Thomas http://toponewithties.blogspot.com</a>

Furious,
Uncontrollable
Neurosis Panic, Chaos, Disorder … my work here is done –unknown

Close to the definition in my link: FUN Fantastically Unbelievable Nonsense [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>