Hi,

i need to Delete/Overwrite Rows in Excel without using Script Task in SSIS.

is it Possible..?

Thanks ,

Chandra Kishore

• Moved by Vicky_LiuMicrosoft contingent staff, Moderator Friday, May 29, 2015 3:45 AM the case related to SSIS

use openquery

http://support.microsoft.com/kb/257819

 

Delete

You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

 

1. Delete an entire record at once or you receive the following error message: 
                 Deleting data in a linked table is not supported by this ISAM. 
   You can only delete a record by blanking out the contents of each individual field.

The following works:

 

Code Snippet

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HRD=YES"')...Sheet1$ AS t

UPDATE OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HRD=YES"')...Sheet1$

SET Name=NULL,

DATE = NULL

WHERE NAME = '[USP_GET_CMDB_INFOS]'

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HRD=YES"')...Sheet1$ AS t

 

Name Date

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[USP_GET_CMDB_INFOS] Feb 7 2008 9:56PM

DDL EVENT RESPONSE Feb 7 2008 9:56PM

long running job Feb 7 2008 9:56PM

(3 row(s) affected)

(1 row(s) affected)

Name Date

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULL NULL

DDL EVENT RESPONSE Feb 7 2008 9:56PM

long running job Feb 7 2008 9:56PM

(3 row(s) affected)

 

 

However, this may not be what you are after since it leaves the blank row inside the excel file.  It doesn't delete the row itself, just clears the data for the row.

Do you want to delete/overwrite ALL the records, or just some?

Hi Chandra,

Based on my test, we can use Execute SQL Task to delete some particular rows in Excel file in SSIS. For example, we can connect the Excel in the Execute SQL Task, then type the following query as SQLStatement to delete the data from particular range [A1:B2] in the excel:
DROP TABLE `Sheet1$A1:B2`

Please note that deleting data in a linked table is not supported by Microsoft Jet Excel ISAM, so we cannot type delete * from `Sheet1$A1:B2` in the Execute SQL Task.

Besides, Excel connection manager does not have a setting that allows overwriting the data in SQL Server Integration Services. Alternatively, we can create a excel file on every execution. We can create a different file each time by adding a unique identifier to the file name, then just load the required data to this excel file; or in order to use the same name every time, we can add an additional step to delete/move the existing file.

Thanks,
Katherine Xiong

Hi All,

Thanks !

Regards,

Chandra