Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

sql