Add a step to Import SSIS

Question

I have an SSIS package that imports a table from one DB to another. I'd like to ADD a pre-cursor step that DROPS the destination table before the import process begins. I'm working with it in Visual Studio. How should I go about doing this? (Let me know what additional info or screen shots would be helpful). Thanks.

Answer 1

Hi @Dom,

You may use execute sql task in front of the loading data task.

In the task, you may drop or truncate the table with t-sql code and then load data.

Reference: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task?view=sql-server-ver16

A simple sample like shown below, if I misunderstand your issue, please incorrect me.

Regards，

Zoe Hui

---

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

Answer 2

That is an odd requirement. Can I assume the table schema is changing? If it isn't then it would likely be better to truncate the table instead. If you delete the table then you'll need to recreate the table before you import. The import process imports into an existing table, it won't create a new one first.

To delete and recreate the table you'll need to use Execute SQL to delete the existing table and create the new table before your Data Flow task runs to actually import the data. That should be pretty straightforward but note that if the table has any relationships, triggers or indices then you'll need to recreate them as well. Like I said a truncation to get rid of the data may be easier.

For a general walkthrough of the process of recreating the table refer to this similar question that was already asked. The answerer walks through the process of creating the table. You just need to add the table deletion before the creation.

Answer 3

You can check whether the table exists before drop it.

DROP TABLE IF EXISTS [dbo].[BI0100RDGP]

CREATE TABLE [dbo].[BI0100RDGP] (

--...