Add a step to Import SSIS

asked 2023-01-11T16:07:31.6966667+00:00
Dom 166 Reputation points

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.

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
2,389 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,839 questions
No comments
{count} votes

Accepted answer
  1. answered 2023-01-12T09:37:50.1366667+00:00
    ZoeHui-MSFT 18,671 Reputation points Microsoft Employee

    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.

    User's image

    Regards,

    Zoe Hui


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


2 additional answers

Sort by: Most helpful
  1. answered 2023-01-11T16:51:22.13+00:00
    Michael Taylor 37,366 Reputation points

    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.


  2. answered 2023-01-12T16:57:55.5333333+00:00
    Jingyang Li 4,361 Reputation points

    You can check whether the table exists before drop it.

    DROP TABLE IF EXISTS [dbo].[BI0100RDGP]

    CREATE TABLE [dbo].[BI0100RDGP] (

    --...

    No comments