Apologies for the long post. I am currently using SSIS 2005 to export data from a sql table to Excel 2007 file using an oledb destination with EXCEL 12.0 XML extended properites. The process is set up to dynamically create the excel file at run time based on user selected values in a table and then dynamically map the source and destination through a script task before running the child package that actually exports the data. The problem I am running into is that all of the data is formatted in the general format. After doing some searching I found that this is a common issue but I couldn't find a definitive solution. Basically I want make it so that the numeric fields are pushed as numeric fields. I tried creating the sheet and then going in and manually formatting it but once the export package ran, it seemed to overwrite the formatting I had set up. What bothers me is that when i create the sheet i use the CREATE TABLE `sheet` format and specify numeric data types but when i go into the the blank excel sheet, the columns are all formatted as general. Does anyone know how I can export this data while maintaining the appropriate format?

If you work with Excel and SSIS for any length of time, you will find that the two do not play well together. Yes, what you recount is a recurring PITA for developers. I have even seen it put all my numerical data in as text, then Excel gives me the stupid little exclamation telling me that I have a number stored as text, even though I did a CREATE TABLE statement and specified numeric data types for the columns. SSIS 2008 may have solved this issue, but I'm not sure.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Yes it's annoying as hell to come this far only to get held up by something so trivial. unfortunately we won't be upgrading to 2008 for some time so I have to figure something out. I had thought about possibly opening the excel file via script task after it had been loaded and then dynamically formatting it and resaving it but I would hope it didn't have to come to something like that.

part of the issue is that Excel is very loose when it comes to Data Types (you can format a column as "Date time" and put in a value of "foo-bar") while SSIS is EXTREMELY tight when it comes to data types. If your process does not need to be so flexible, could you get away with creating a template Excel file with the required column names, then just copy that file over to a folder where SSIS would populate it?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I don't think using a template file would work and here is why. I tried creating the file with the header row and then manually going in and formatting the columns the way that I wanted them. I then saved the blank excel file with all of the formatting and then kicked off the export. When I opened up the file afterwards, the formatting remained in the header row, but all of the data that was exported was in general format. Its almost as if the export had overwritten any formatting I had set up.