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 Wednesday, March 5, 2008 11:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 10:53 PM
Points: 48, Visits: 245
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
Post #464651
Posted Thursday, March 6, 2008 7:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
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.


Imagination is more important than knowledge-Albert Einstein
Post #465135
Posted Thursday, March 6, 2008 10:31 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
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.
Post #464998
Posted Friday, March 7, 2008 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 10:53 PM
Points: 48, Visits: 245
Thanks to both for the reply. I would try the options and let you know if I have any problem.

Thanks,
-Amith Vemuganti
Post #465920
Posted Wednesday, April 16, 2008 5:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:33 AM
Points: 128, Visits: 158
good day all,

could someone please help me in order to overwrite excel files in SSIS Packages?
Post #485574
Posted Tuesday, June 17, 2008 2:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:05 AM
Points: 15, Visits: 186
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.
Post #518560
Posted Wednesday, June 18, 2008 1:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Consider using a drop table/create table command, which will effectively delete the worksheet and replace this with a new one.

~PD
Post #518794
Posted Wednesday, June 18, 2008 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 PM
Points: 49, Visits: 246
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.
Post #519350
Posted Thursday, June 19, 2008 2:06 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
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
Post #519652
Posted Thursday, June 19, 2008 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 PM
Points: 49, Visits: 246
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?
Post #520059
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse