July 27, 2010 at 3:27 pm
Hi,
I'm using SSIS to export data from a SQL table to an Excel spreadsheet daily. The first row of the Excel spreadsheet is the header and I would like to delete the records before writing the new records for the current day. I am using an Execute SQL Task and my ConnectionType is set to Excel, my Connection in the General Page is set to the Excel Connection Manager, SQLSourceType is set to Direct Input and the SQLStatement is: DELETE FROM 'Query' GO. The Task generates the following error:
[Execute SQL Task] Error: Executing the query DELETE FROM `Query$` failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
July 28, 2010 at 9:09 am
July 28, 2010 at 3:00 pm
Technically, you can't delete records - you can only empty them out using an update statement like this:
update table_name
set field1 = null,
field2 = null,
fieldn = null
where field1 = unique_value
Obviously, not the most ideal method most of the time. If you're looking for a blanket delete of the data in the spreadsheet, you can create a blank template file and use file copy/move/delete tasks to overwrite the original file. Another method would be to have the real data on a hidden sheet/tab and have another sheet/tab use it as a filtered data source.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
July 28, 2010 at 6:51 pm
Thanks for all of your inputs -- it's really appreciated. EJ, I reviewed the links you shared and found a solution that works. Basically I am using the File System Task to copy the report template and writing the records to the empty template. Works like a charm!!
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply