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
Amith Vemuganti
Amith Vemuganti
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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
SQLdevotee
SQLdevotee
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 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
born2bongo
born2bongo
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 351
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.
Amith Vemuganti
Amith Vemuganti
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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
RamaA
RamaA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 271
good day all,

could someone please help me in order to overwrite excel files in SSIS Packages?
iowaind
iowaind
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
pduplessis-723389
pduplessis-723389
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3025 Visits: 400
Consider using a drop table/create table command, which will effectively delete the worksheet and replace this with a new one.

~PD
fstop
fstop
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 316
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.
born2bongo
born2bongo
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 351
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
fstop
fstop
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 316
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?
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