SSIS package

Question

SSIS package

jeberhard-623651

SSCrazy

Points: 2644
More actions
December 22, 2011 at 1:10 pm

#262153

Why can't SSIS export data from a SQL stored procedure to an Excel file, overwriting the excel file each time it runs? AM I missing something?

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

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

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

jeberhard-623651 (12/22/2011)
Why can't SSIS export data from a SQL stored procedure to an Excel file, overwriting the excel file each time it runs? AM I missing something?

I guess it's a feature and not a bug. If the Excel file already exists, the data will just be appended at the end.

If you want to achieve the behaviour you mention, you can simply delete the Excel file if it exists (before you transfer the data). The file will then be recreated and populated.

Martin.

jeberhard-623651 SSCrazy Points: 2644 More actions · Answer 2

Thanks. I think I tried deleting the excel file first and then got mapping errors in the data flow task. I had manually deleted the file from the directory so maybe if I make it a part of the package it will work OK.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 3

jeberhard-623651 (12/23/2011)
Thanks. I think I tried deleting the excel file first and then got mapping errors in the data flow task. I had manually deleted the file from the directory so maybe if I make it a part of the package it will work OK.

That should work as far as I know. If it doesn't though, your other option is to create an empty template and make a copy before populating it with data. I find that this usually works well with Excel destinations...it is a little cumbersome, but works nevertheless.

jeberhard-623651 SSCrazy Points: 2644 More actions · Answer 4

You would think Microsoft would make getting data from a SQL database to an Excel file easier than this. I still haven't gotten it to work but I'm sure it must be possible.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 5

jeberhard-623651 (12/23/2011)
You would think Microsoft would make getting data from a SQL database to an Excel file easier than this. I still haven't gotten it to work but I'm sure it must be possible.

It does work, but the majority of the issues/limitations are because of the JET driver which we have to use to move data to/from Excel.

I personally don't know of anybody that's particularly fond of the JET driver, and I also think it hasn't been updated in a very long time.

kramaswamy SSCoach Points: 18181 More actions · Answer 6

The empty template approach is the one I use as well. I find it makes things easier to deal with, since I can set up the mappings beforehand, and then have the template reset the file each time.