trying to write to an excel file via ssis and it wount overwrite prev days info

Question

trying to write to an excel file via ssis and it wount overwrite prev days info

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
December 28, 2009 at 3:28 pm

#143848

I have a ssis package that i'm building. There is a portion that loads an excel file w/ data from a select i wrote.
The issue is i can't get it to overwrite the previous days data in the excel. Is there a flag i'm missing or something?
On a side note.
I was a dts wiz and am finding that ssis is far less intuitive. IIt was a friggin nightmare to try and bring down an excel file to a db table. I had to use a "data conversion" dataflow transformation. Talk about making the simple very combersome and complex. Give me a break.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

BaldingLoopMan (12/28/2009)
I have a ssis package that i'm building. There is a portion that loads an excel file w/ data from a select i wrote.
The issue is i can't get it to overwrite the previous days data in the excel. Is there a flag i'm missing or something?
On a side note.
I was a dts wiz and am finding that ssis is far less intuitive. IIt was a friggin nightmare to try and bring down an excel file to a db table. I had to use a "data conversion" dataflow transformation. Talk about making the simple very combersome and complex. Give me a break.

There is no option to to select overwrite. I would recommend you setup a separate template Excel file and use this as a starting point for your result creation.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

A possible alternative is to delete and recreate the sheet you are working with, using Execute SQL tasks against an Excel Connection Manager connection ...

DROP TABLE 'sheetname'

followed by

CREATE TABLE 'sheetname' (ID Varchar(10), Description Varchar(100))

--or whatever.

The change from DTS to SSIS is a big one. If you expected just to be able to 'get on with it' after upgrading, I am not surprised that you are feeling a bit disheartened. But if you were to ask people who are well experienced with both, I'm pretty damn sure that SSIS would win hands-down. You just have to learn some new ways of doing things.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Justin Lezon SSCommitted Points: 1937 More actions · Answer 3

Please review the following discussion:

http://www.sqlservercentral.com/Forums/Topic464651-148-1.aspx

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

i went ahead and did the remove the xls tne overwrite it w template then write the data to the file shell game.

Seems a bit much though just to overwrite data. I think i'm going to impliment it writting to a csv instead so i dont have to deal w/ the extra steps.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

Much easier - I always choose CSV over Excel, given the option.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin