Error at recreating new Excel table in SSIS after deletion

Question

Greetings,

I was trying to make a SSIS package in order to recreate an Excel file which contains some result of a SQL query I'm executing. My architecture is like this:

In "Borrar tabla Excel" I delete the existing Excel table via a DROP TABLE [Excel Destination] (only way to achieve that, because EXCEL is not compatible with DELETE FROM), but when I try to recreate the table with my columns, I get this error:

[Execute SQL Task] Error: Executing the query "CREATE TABLE Excel Destination (entidad NVARCHAR..." failed with the following error: "La tabla 'Excel Destination' ya existe.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What can I do to recreate my Excel every time I execute my SSIS package?

I'm using SSIS at Visual Studio 2019 and my target server is SQL Server 2016.

Thanks in advance!

Answer 1

Hi Frank Sebastián Franco Hernández

failed with the following error: "La tabla 'Excel Destination' ya existe."

From the error message, it seems that the table still exists so you cannot create the same table.

You may use only one execute sql task component to drop and create table with below sample code, there is no need to use two.

 DROP TABLE IF EXISTS SQLTest
  CREATE TABLE SQLTest (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
  insert into SQLTest values
  ('1','2','3','4','5') 

Or you may use truncate table first and then load data you want to the sql.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.