How to configure an Execute SQL task to drop and re-create a destination table (Enterprise Manager)

To configure an Execute SQL Task to drop and re-create a destination table

1. From the Task toolbar, drag an Execute SQL task onto the Data Transformation Services (DTS) Designer design sheet. 
   
   
2. In the Description box, type Drop Dest Table.

After you configure the task, that text will display on the design sheet, under the Execute SQL task icon.

3. In the Existing connection list, click Cn2.

   In this example, you can use either connection because both Cn1 and Cn2 connect to the same database. However, it is better practice to use the destination connection.

4. In the SQL statement text box, type the following SQL code:

   IF EXISTS (SELECT * from sysobjects 
      WHERE id = object_id(N'[Northwind].[dbo].[Categories2]') AND
      OBJECTPROPERTY(id, N'IsUserTable') = 1)
      DROP Table [Northwind].[dbo].[Categories2]
   GO
   
   CREATE TABLE [Northwind].[dbo].[Categories2]
   (
      [CategoryID]  [int]  IDENTITY (1,1)  NOT NULL  PRIMARY KEY,
      [CategoryName]  [nvarchar]  (15)  NOT NULL,
      [Description]  [ntext],
      [Picture]  [image]
   )
   GO
   

This SQL code checks for the presence of the destination table. If the table does not exist, it is created. If the table exists, it is dropped and re-created. Without this package step, the same data from the source table is appended to the destination table every time the package is run.

5. Click OK to save the configuration settings and SQL code for the Execute SQL task.