SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Overwriting Excel File with SSIS package


Overwriting Excel File with SSIS package

Author
Message
iowaind
iowaind
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 186
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.
born2bongo
born2bongo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 351
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.
fstop
fstop
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 316
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.
iowaind
iowaind
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 186
Yep - Missed that option. I have removed the delete file step and it still works good.
sundevilluvr
sundevilluvr
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
sundevilluvr
sundevilluvr
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
born2bongo
born2bongo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 351
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
ewancampbell1
ewancampbell1
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
born2bongo
born2bongo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 351
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
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search