Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Overwriting Excel File with SSIS package Expand / Collapse
Author
Message
Posted Thursday, June 19, 2008 2:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:54 PM
Points: 15, Visits: 181
Born2Bongo - Your response is dead on. I have a blank template called template.xls with the column headers in place (no data). The output is know as report.xls (based on template.xls but with all of the data). I basically do the following:

1. Delete the existing report.xls (from the last time the SSIS package ran).
2. Copy the template.xls to the old location that report.xls was at.
3. Rename template.xls to report.xls.
4. Run the process that dumps the output to report.xls.

Works great.
Post #520246
Posted Friday, June 20, 2008 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 5:29 AM
Points: 157, Visits: 350
iowaind - You don't have to delete report.xls. Having a file copy task with overwrite set to yes will do it automatically.

fstop - I'm pretty new to SSIS and SQL Server myself, but with a long background in Excel and Access. I've not used import and export myself and I' m finding it hard to visualise what you are describing.

I suggest you start from new. Create a new empty package (and a new project if that helps). Drag a File task into it and set up as I described above, then drag a data flow task in and edit this.

It will need a source, and a destination component at least, but probably some transformation stuff in the middle. Do a simple version at first and gradually enhance it till you get what you want. That way you should be able to work out where you are going wrong.
Post #520516
Posted Monday, June 23, 2008 11:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Thank you for your suggestion. I tried to go back to square one but I think it made things worse. I'm going to start a new topic which will explain my dilemna in detail with hopes that an SSIS guru will assist me with getting this very frustrating package to do what I need.
Post #522064
Posted Friday, June 27, 2008 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:54 PM
Points: 15, Visits: 181
Yep - Missed that option. I have removed the delete file step and it still works good.
Post #525176
Posted Thursday, September 11, 2008 1:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 3:31 PM
Points: 7, Visits: 26
Born2bongo,first off thanks for your suggestions.

I tried both - 1. creating the file system task to copy the template( and using this new file as the destination) and 2. creating the execute sql tasks to drop and create an excel spreadsheet.

By #1 did not output anything after I ran the package; even though the results show that some data was written out
By #2 I get the results, but it is not at the first row. The drop task apparently deletes the rows and the new results are appended and not overwritten over those blank rows. This gives me lots of blank rows as I keep running the package.

Any ideas how to solve this?

Thanks much.
Post #568073
Posted Friday, September 12, 2008 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 3:31 PM
Points: 7, Visits: 26
OK! got it to work. I have a File Task to copy a template over to my destination in a package. Then another package that has an Execute SQL Task to create the sheet and the data flow. I run the first package to copy the template and then the data flow package. Works wonderfully well.
Post #568948
Posted Monday, September 15, 2008 2:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 5:29 AM
Points: 157, Visits: 350
Cool. That's one I've not tried yet but sounds like a slick solution and one I could make use of fairly soon.

I've only just read both your posts, and when I saw your comment about option 1, the first thing I wanted to know was "did you check the whole sheet?". As you now realise, there are some quirks about how the sheet is handled.

I've been caught out like that. I set up a template with a sheet containing just headings, saw the package write a few hundred rows, opened the workbook and all I got was the headings. However, scroll down, and there's my data at row 5000 or somewhere near. When I create a template now, I make sure I've deleted all empty rows, and then put the headings in.

B2B
Post #569293
Posted Thursday, September 18, 2008 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 4:04 AM
Points: 1, Visits: 109
hey

Also been struggling for days with this issue.

A simple task that used to be easily dealt with in SQL200 DTS - just overwrite or create a brand new destination spreadsheet each time a package runs - simple.
I used to be able to create a packahge to export data to excel in 5 minutes with DTS.
Now it takes days and i have to define the detail of dozens more objects.
This is nonsense.

Simple tasks being made overly complicated doesnt help anyone.
Creating excel templates, deleting sheets before your package starts, connections having to be named, connection managers needing to be redfeined for every opackage that uses them.
Errors about excel sheets of a certain name already exisiting causing packages to fail - i dont care just overwrite the excel file if it exists.

Not impressed :-(
I thought these tools were supposed to make a DBA's life easier not harder.
Post #571646
Posted Thursday, September 18, 2008 5:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 5:29 AM
Points: 157, Visits: 350
Yep. Know what you mean. Been doing it on and off for a year and still very new really. If you think that's bad you wait till this happens:

Every time I make a change to the data that I want output to the spreadsheet, SSIS kindly goes through my output, resets all STR columns to WSTR, and then fails the package because they are incompatible. Nice...

I think I tried casting the data on extract once, and it probably worked, but that's just as big a pain. But that's Microsoft for you
Post #571651
Posted Friday, November 21, 2008 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
We have a similar issue. Daily we replace a spreadsheet with a template spreadsheet and write out the new data set. All that works fine. The problem is that three of the columns should be numeric but excel keeps changing to text. The first time the package was created the initial "table" had the correct datatype. We are selecting integers and outputting integers. But in subsequent runs of the package the datatype is changed to text. This is a real problem. Daily someone has to edit the spreadsheet and convert the text to numeric -- which defeats the purpose of a scheduled job.
Does anyone know how to force the output into excel to retain the datatype? I have already saved the package as a VBS script and modified the output datatype. It appears to be an Excel issue. Thank you much for any help you can give.
Post #606571
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse