March 5, 2008 at 11:39 am
Hi All,
I have an SSIS package which exports the data to an Excel file using Excel Destination component. Could any one please let me know if there is any way to overwrite the excel file each time I run the package with out having to modify the package every time I run.
Thanks in advance,
-Amith Vemuganti
March 6, 2008 at 7:21 am
If you want to MAKE SURE that you want the new data to be entered everytime,(in another way overwriting) You should drag a File System Task and put it on the very beginning of your package. In the Control flow before your data flow. Then select delete file content in it.
So, basically everytime you run this package, before anything happens, it will delete the content so that new datas are inserted. SO, you would not have to worry about whether it got overwritten or not. The data will be fresh for you each time.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 6, 2008 at 10:31 am
Hi Amith
I'm pretty new to this but I have been building a package with an Excel destination, and it has automatically overwritten the file every time.
I'm not sure if this is the best way, but it works for me.
I have a suite of 3 packages. The first package is a file system task that copies an Excel file from one location to another. The file system task I use is Copy file, and in the Destination Connection details I have set OverwriteDestination to True. Subsequent packages then write data to different sheets in the workbook.
For some background, the file being copied is used as a template (it is not an Excel Template (.xlt), but is used as the basis for all future output). The reason I do this is that the excel workbook contains functionality like Pivot Tables and macros, and it's the only way I know to replicate them in the output.
March 7, 2008 at 8:36 am
Thanks to both for the reply. I would try the options and let you know if I have any problem.
Thanks,
-Amith Vemuganti
April 16, 2008 at 5:55 am
good day all,
could someone please help me in order to overwrite excel files in SSIS Packages?
June 17, 2008 at 2:05 pm
The 'File System Task' does not have an option for 'Delete File Content'. Instead you must recreate the file each time via a copy or other method.
June 18, 2008 at 1:18 am
Consider using a drop table/create table command, which will effectively delete the worksheet and replace this with a new one.
~PD
June 18, 2008 at 12:53 pm
To continue on this topic with a slightly different slant, I would like to create a new Excel file each time the package runs. It seems that I am forced, however, to name an existing Excel file in DestinationConnectionExcel. One caveat to this is that I also need the Excel file to have a header row of column names. I'm not sure how to do this using packages. Any ideas would be greatly appreciated.
June 19, 2008 at 2:06 am
I think I more or less posted the answer to this above.
Use an empty excel file as a kind of template - it will be empty except for the column headers. Use a file system task to copy this template to a new location and you can give it a different name at this point. It then becomes the Excel destination you need for your job.
The file system task is a Copy File task with a source file connection pointing to the spreadsheet you are using as a template, and the destination file connection giving the name and location of your intended output. I have set my copy file task to allow overwriting of the destination, but you can decide if that's what you want.
The file system task is then followed by a data flow task, and the final component of this is the Excel Destination. The Excel sheet parameter points to the name of the sheet having the column headings, and the destination has an excel connection with the name and path of the excel output file. Choose your Excel version and check the box to say 'First row has column names'.
That should do it
June 19, 2008 at 11:23 am
Thanks for that explanation. As an SSIS beginner, that was very helpful. Unfortunately, I can't test it all the way through because the package keeps erroring out in the Preparation SQL Task in the Control Flow. This task was set up automatically when I ran the SSIS Import Export Wizard to create the package.
The task creates an interim table called Query that contains the column names relating to the Excel output file. In the properties, the Destination for the SQL Statement Connection parameter is DestinationConnectionExcel, which I created to handle the output of the SQL query. When I run the package, it fails on the "Create Table" statement stating that the table already exists, yet there seems to be no setting to delete or overwrite an existing table.
Do you know how I can get past this issue?
June 19, 2008 at 2:25 pm
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.
June 20, 2008 at 4:50 am
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.
June 23, 2008 at 11:16 am
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.
June 27, 2008 at 10:30 am
Yep - Missed that option. I have removed the delete file step and it still works good.
September 11, 2008 at 1:18 pm
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.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply