SSMS Import Export Wizard Error: Cannot Delete Rows in Destination Table

Question

Hi everyone,

Here's the background: I built ETL in the database, schedule jobs to auto refresh, and then I go manually copy paste the table into Excel file to share with my team. Now I want to automate the export process. But the company doesn't have Microsoft Visio Studio. I guess I have to create SSIS package through SQL Server Import and Export Wizard, then use SQL Server Agent to schedule jobs to run the package.

In SQL Server Import and Export Wizard, it works fine if I choose "Create destination table" or "Append rows to the destination table".

But I don't want to have a lot of duplicated records in my Excel file, so I was try to use "Delete rows in destination table". But it gave me the following error:

I researched online, and all the solutions that I found suggest me to setup in "Execute SQL Task Editor". But I don't have Microsoft Visio Studio. Is there any other way to fix this error?

Any help would be appreciated.

Thanks.

Answer 1

Hi @Jay Jiang ,

Actually, what you use to design the ETL package is SSDT which is a part of VS.

The EULA of VS 2019 community edition has a specific section that allows enterprise users to use it for the SSDT workload.

FOR MICROSOFT VISUAL STUDIO COMMUNITY 2019:

Any number of your users may use the software only for Microsoft SQL Server development when using the SQL Server Data Tools or the extensions “Microsoft Analysis Services Projects”, “Microsoft Reporting Services Projects”, or “SQL Server Integration Services Projects” to develop Microsoft SQL Server database projects or Analysis Services, Reporting Services, Power BI Report Server, or Integration Services projects.

See VS- license.

About SSDT, please check download-sql-server-data-tools-ssdt.

Please install VS2019 or earlier, VS2022 do not support BI project temporarily.

VS-Downloads

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Jay Jiang ,

OLE DB provider only allows to insert or update records to excel sheet. It does NOT allow delete operations.

You could not use "Delete rows in destination table" to delete the data in the excel.

SQL Server Import and Export Wizard is a simple way to copy data from a source to a destination.

I'd suggest to install the VS to develop ETL packages, if company policy do not allow the installation, you may try with excel VBA to meet your requirement.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.