Programmatically Drop and Recreate Foreign Keys with SSIS

By: Ray Barley   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Integration Services Development

Problem

I'm using SSIS to perform periodic data loads. In some cases the data load is a complete refresh of the data; i.e. we truncate the existing tables then perform the load. The one issue we have is that there are many foreign keys and this stops us from being able to truncate the tables. What we need is a way to automatically drop the foreign keys, perform the data load, then recreate the foreign keys. Can you provide a solution?

Solution

If you've ever written the T-SQL commands to drop and add foreign keys, you know there has to be a better way. In this tip I'll be using SQL Server 2008 R2 and will walk through an SSIS solution that performs the following steps:

• Script out the foreign keys that need to be dropped and added
• Execute the scripts to drop the foreign keys
• Perform the data load
• Execute the scripts to add the foreign keys

Each of the above steps will be implemented in its own SSIS package and I'll have a master package to run everything. My goal is to create something that can easily be added to a new or existing extract, transform and load (ETL) process.

SSIS Master Package

A master package is an SSIS package that orchestrates a process by executing a series of SSIS packages using the Execute Package task. The benefit of a master package is that it allows you to create individual SSIS packages that perform a single task; e.g. script out foreign keys, drop foreign keys, load tables, add foreign keys, and so on. The master package is also a great way to build an ETL process that can be restarted at any point after a failure. SSIS has a built-in feature call Checkpoints which will enable you to do this; take a look a the tip Checkpoints in SQL Server Integration Services SSIS to restart from the point of failure for the details.

The control flow for the master package is shown below:

I will cover the details on each package in the sections that follow. The PLACEHOLDER FOR ETL PROCESSING is just that; I'm not going to go through any Data Flows where we extract data, transform it, and load it into a table. My focus is on dealing with dropping and adding foreign keys.

INITIALIZE

The INITIALIZE package creates the tables used in the solution. The control flow for the package is shown below:

The following tables are created:

The ETL_TABLES_TO_LOAD table has the list of tables to be loaded. The LOAD column is a bit type; set it to 1 to load a table or 0 to skip it. The list of tables to be loaded is ordered by the LOAD_SEQUENCE column so that the tables referenced by foreign keys can be loaded first. The PLACEHOLDER FOR ETL PROCESSING container in the master package would iterate over the list of tables to load and call the appropriate SSIS package to load each table.

The Populate ETL_TABLES_TO_LOAD SQL Execute SQL task inserts rows into the ETL_TABLES_TO_LOAD table for the CUSTOMER and STATE_LOOKUP tables.

The CUSTOMER and STATE_LOOKUP tables are examples of tables that we would like to periodically load. The CUSTOMER table has a foreign key reference to the STATE_LOOKUP table.

The Execute SQL tasks that create the tables do so if the table does not already exist; e.g.:

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ETL_TABLES_TO_LOAD')
CREATE TABLE [dbo].[ETL_TABLES_TO_LOAD](
 [TABLE_NAME] [sysname] NOT NULL,
 [LOAD] BIT,
 [LOAD_SEQUENCE] [int] NULL
) 

Wherever possible I like to include this type of logic in an SSIS package so that when it is executed it can create tables or other database objects that are required instead of having a separate installation program.

SCRIPT FOREIGN KEYS

The SCRIPT FOREIGN KEYS package generates T-SQL commands to drop and add any foreign keys that will prevent us from truncating the tables that are selected to be loaded. In our simple example the CUSTOMER table has a foreign key that references the STATE_LOOKUP table. If we want to truncate and load the STATE_LOOKUP table, then we need to drop the foreign key on the CUSTOMER table that references the STATE_LOOKUP table.

The control flow for the package is shown below:

The following are the main points about the control flow:

• The ETL_FOREIGN_KEY_SCRIPT table contains the T-SQL commands to drop and add foreign keys; I create the table if it doesn't exist and truncate it if it does
• The logic to create the drop and add foreign key T-SQL commands is implemented in the stored procedure ETL_SCRIPT_FOREIGN_KEYS
• The T-SQL commands are written out to the ETL_FOREIGN_KEY_SCRIPT table
• The Get Tables to Load Execute SQL task queries the ETL_TABLES_TO_LOAD table and returns the list of tables that are selected for loading
• The Process each table to be loaded Foreach Loop Container iterates over the list of tables to be loaded and generates the necessary drop and add foreign key T-SQL commands

The ETL_SCRIPT_FOREIGN_KEYS stored procedure is based on the tip Disable, enable, drop and recreate SQL Server Foreign Keys. I took the code from the tip and changed it to write the T-SQL commands to drop and add the foreign keys that reference a table to the ETL_FOREIGN_KEY_SCRIPT table. Take a look at the earlier tip for the complete details. The stored procedure generates the T-SQL commands to drop and add any foreign keys for a given table.

The above control flow has two tasks that work together to make sure that the ETL_SCRIPT_FOREIGN_KEYS stored procedure exists:

• Create ETL_SCRIPT_FOREIGN_KEYS placeholder
• Alter ETL_SCRIPT_FOREIGN_KEYS procedure

The following is the T-SQL code in these tasks:

IF NOT EXISTS (
 SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME = 'ETL_SCRIPT_FOREIGN_KEYS'
)
 EXEC sp_ExecuteSQL 
 N'CREATE PROCEDURE dbo.ETL_SCRIPT_FOREIGN_KEYS 
 AS 
 BEGIN 
  PRINT ''Placeholder for ETL_SCRIPT_FOREIGN_KEYS procedure'' 
 END;'
 
ALTER PROCEDURE dbo.ETL_SCRIPT_FOREIGN_KEYS
 @TABLE_NAME VARCHAR(128)
, @SCHEMA_NAME VARCHAR(128) 
AS
BEGIN
-- not shown for brevity; download link at the end of this tip
END 

If the stored procedure doesn't exist, it gets created with just the PRINT statement. The ALTER PROCEDURE statement always runs; this allows us to change the stored procedure in the package and have it be updated automatically when the package runs. It also allows us to update the stored procedure without affecting any permissions that may have been set.

The following is an example of the generated T-SQL commands in the ETL_FOREIGN_KEY_SCRIPT table:

IF EXISTS (
 SELECT 1 FROM sys.foreign_keys 
 WHERE name = 'FK__CUSTOMER__STATE___0EA330E9' 
 ) 
 ALTER TABLE [dbo].[CUSTOMER]  
 DROP CONSTRAINT [FK__CUSTOMER__STATE___0EA330E9]
 
IF NOT EXISTS (
 SELECT 1 FROM sys.foreign_keys 
 WHERE name = 'FK__CUSTOMER__STATE___0EA330E9' 
 ) 
 ALTER TABLE [dbo].[CUSTOMER]  
 WITH  CHECK  ADD CONSTRAINT [FK__CUSTOMER__STATE___0EA330E9] 
 FOREIGN KEY ([STATE_ID]) 
 REFERENCES [dbo].[STATE_LOOKUP] ([STATE_ID]) 
 ON UPDATE  NO ACTION  ON DELETE  NO ACTION

Note the checks that are in the generated T-SQL commands: make sure the foreign key exists before dropping it and make sure the foreign key doesn't exist before adding it. This is an attempt to have the package run successfully in a variety of situations; e.g. when a package fails and is restarted.

DROP FOREIGN KEYS

The DROP FOREIGN KEYS package queries the ETL_FOREIGN_KEY_SCRIPT table and retrieves every row where the COMMAND = 'DROP'. It then iterates over this list and executes each T-SQL command to drop a foreign key. The control flow for the package is shown below:

ADD FOREIGN KEYS

The ADD FOREIGN KEYS package queries the ETL_FOREIGN_KEY_SCRIPT table and retrieves every row where the COMMAND = 'ADD'. It then iterates over this list and executes each T-SQL command to add a foreign key. The control flow for the package is shown below:

Next Steps

• This tip shows an example of how you can build individual and reusable SSIS packages then use a master package to orchestrate executing the individual packages.
• The goal was to create the solution in such a way as to facilitate adding it to a new or existing ETL process.
• You can download the sample here.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips