Drop and Create Primary Key, Foreign Key, Indexes in SSIS dynamically

I managed to create SSIS POC  for Drop & Create constraints and indexes dynamically.

I have created the following tables and stored procedures to arrive at the solution:

Two tables to store Index and Constraints details:
    1.  dbo.TableConstraints: This table holds the detail of all constraints (PK's & FK's)  created in a database.
    2. dbo.TableIndex: This table contains the detail of all indexes created in a database.

Nine stored procedures to drop and create:
    1. spInsertConstraints : Execute this procedure to populate 'dbo.TableConstraints', when you make any changes to table or database structure this has to be executed.
    2. spInsertIndexes: Execute this procedure to populate 'dbo.TableIndex', when you make any changes to table or database structure this has to be executed.
    3. spDropRefFKs: Drops all reference relationship in a table.
    4. spDropPKFKConstarints: Drops all constraints in a table.
    5. spDropAllIndexes: Drops all indexes in a table.
    6. spBuildPKConstarints: Rebuilds PK in atable (gets details from 'dbo.TableConstarints').
    7. spBuildFKConstarints: Rebuilds all Foreign Keys in a table (gets details from 'dbo.TableConstarints').
    8. spBuildFKRefs: Rebuilds all reference relationship in a table (gets details from 'dbo.TableConstarints').
    9. spBuildAllIndexes: Rebuilds all indexes in a table (gets index details from 'dbo.TableIndex').

Initial steps to be followed:

    1. Execute stored procedure 'spInsertConstraints'. It is one time run until there is no changes in database. If you made any changes to database or tables, this has to be re-executed.
    2. Execute stored procedure 'spInsertIndexes'. It is one time run until there is no changes in database. If you made any changes to database or tables, this has to be re-executed.

Script for Stored Procedure

USE [ListSelect20_be]
GO
/****** Object:  Table [dbo].[TableIndex]    Script Date: 07/05/2011 12:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableIndex](
 [ObjectID] [bigint] NULL,
 [TableName] [varchar](100) NULL,
 [SchemaName] [varchar](50) NULL,
 [IndexName] [varchar](100) NULL,
 [IndexID] [int] NULL,
 [IndexType] [int] NULL,
 [IXDescription] [varchar](100) NULL,
 [ColumnName] [varchar](100) NULL,
 [IsUnique] [varchar](50) NULL,
 [IsUniqueConstraint] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TableConstraints]    Script Date: 07/05/2011 12:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableConstraints](
 [ObjectID] [bigint] NULL,
 [Constraint_Catalog] [varchar](100) NULL,
 [Constraint_Schema] [varchar](100) NULL,
 [Constraint_Name] [varchar](100) NULL,
 [Table_Catalog] [varchar](100) NULL,
 [Table_Schema] [varchar](100) NULL,
 [Table_name] [varchar](100) NULL,
 [Constraint_Type] [varchar](100) NULL,
 [Is_Deferrable] [nchar](10) NULL,
 [Initially_Deferrable] [nchar](10) NULL,
 [Column_Name] [varchar](100) NULL,
 [Ordinal_Position] [int] NULL,
 [TypeDesc] [varchar](100) NULL,
 [Is_Unique] [varchar](50) NULL,
 [Is_Unique_Constraint] [varchar](50) NULL,
 [ParentObjectID] [bigint] NULL,
 [ParentObject] [varchar](100) NULL,
 [ReferenceObjectID] [bigint] NULL,
 [ReferenceObject] [varchar](100) NULL,
 [ReferenceColumnID] [bigint] NULL,
 [ReferenceColumn] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[spDropPKFKConstarints]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================================================
-- Author   : Mahadevan
-- Create date: June 23, 2011
-- Description: Remove all constraints PK and FK in a table
-- =========================================================
CREATE PROCEDURE [dbo].[spDropPKFKConstarints]
@Database VARCHAR(50),
@Schema VARCHAR(50),
@Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;
    BEGIN
  DECLARE @SQL NVARCHAR(255)
  WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_CATALOG = @Database
      AND TABLE_NAME = @Tablename AND CONSTRAINT_SCHEMA = @Schema)
  BEGIN
   SELECT    @SQL = 'ALTER TABLE ' + @Tablename + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
   FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE   CONSTRAINT_CATALOG = @Database AND TABLE_NAME = @Tablename AND CONSTRAINT_SCHEMA = @Schema
   ORDER BY CONSTRAINT_TYPE DESC
   EXEC    sp_executesql @sql
  END
 END
END
GO
/****** Object:  StoredProcedure [dbo].[spDropAllIndexes]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author:  Mahadevan
-- Create date: 26/6/2011
-- Description: To drop all the indexes in a table
-- =================================================
CREATE PROCEDURE [dbo].[spDropAllIndexes]

 @Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;

 DECLARE @IndexName VARCHAR(128)
 DECLARE @Table VARCHAR(128)
 DECLARE [GetIndexes] CURSOR FOR
  SELECT  I.[name] AS [Index],
     S.[name] AS [Table]
  FROM  [sysindexes] I
  INNER JOIN [sysobjects] S
  ON   I.[id] = S.[id]
  WHERE  I.[name] IS NOT NULL
  AND   S.[type] = 'U'
  AND   I.INDID > 0
  AND   I.INDID < 255
  AND   (I.STATUS & 64)=0
  AND S.[Name] = @Tablename
  ORDER BY [Table]
 OPEN [GetIndexes]
 FETCH NEXT FROM [GetIndexes] INTO @IndexName, @Table
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @SQL NVARCHAR(255)
  BEGIN
  SELECT @SQL =  'DROP INDEX [' + @IndexName + '] ON [' + @Table + '] '
        + 'WITH ( ONLINE = OFF );'
  END
  --PRINT @SQL
  EXEC    sp_executesql @SQL
  FETCH NEXT FROM [GETIndexes] INTO @IndexName, @Table
 END
 CLOSE  [GetIndexes]
 DEALLOCATE [GetIndexes]
  
END
GO
/****** Object:  StoredProcedure [dbo].[spBuildPKConstarints]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================================================
-- Author   : Mahadevan
-- Create date: June 24, 2011
-- Description: Create PK in a table
-- =========================================================
CREATE PROCEDURE [dbo].[spBuildPKConstarints]

@Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;
    BEGIN
  DECLARE @Table VARCHAR(50)
  DECLARE @ConstraintName VARCHAR(50)
  DECLARE @ConstraintDesc VARCHAR(50)
  DECLARE @ConstraintColumn1 VARCHAR(50)
  DECLARE @ConstraintColumn2 VARCHAR(50)
  
  DECLARE [GetConstraints] CURSOR FOR
    SELECT  A.TABLE_NAME
      ,A.CONSTRAINT_NAME
      ,A.TYPEDESC
      ,A.COLUMN_NAME AS Column1
      ,B.Column_Name AS Column2
    FROM    TableConstraints A
    LEFT JOIN (SELECT OBJECTID, Column_Name FROM TableConstraints WHERE Ordinal_Position = 2) B ON B.OBJECTID = A.ObjectID
    WHERE  A.Ordinal_Position=1 AND  A.Table_name = @Tablename AND   A.Constraint_Type = 'PRIMARY KEY'
  OPEN [GetConstraints]
  FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName,  @ConstraintDesc, @ConstraintColumn1, @ConstraintColumn2
  WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE @SQL NVARCHAR(1000)
   IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @Table
    AND CONSTRAINT_NAME = @ConstraintName)
   BEGIN
   IF (@ConstraintColumn2 IS NULL)
   SELECT    @SQL = 'ALTER TABLE ' + @Table + ' ADD CONSTRAINT '+ @ConstraintName
         + ' PRIMARY KEY '  + @ConstraintDesc + '(' + @ConstraintColumn1 + ' ASC)'
         + ' WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF'
         + ',SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF'
         + ',ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'

   ELSE
   SELECT    @SQL = 'ALTER TABLE ' + @Table + ' ADD CONSTRAINT '+ @ConstraintName
         + ' PRIMARY KEY '  + @ConstraintDesc + '(' + @ConstraintColumn1 +','+ @ConstraintColumn2+')'
         + ' WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF'
         + ',SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF'
         + ',ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
   END
   --Print @SQL
   EXEC sp_executesql @SQL
   FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName,  @ConstraintDesc, @ConstraintColumn1, @ConstraintColumn2
  END
  CLOSE  [GetConstraints]
  DEALLOCATE [GetConstraints]
 END
END
GO
/****** Object:  StoredProcedure [dbo].[spBuildFKRefs]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author:  Mahadevan
-- Create date: 26/6/2011
-- Description: To rebuild all the FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spBuildFKRefs]

 @Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;

 BEGIN
 DECLARE @Table  VARCHAR(128)
 DECLARE @ConstraintName VARCHAR(128)
 DECLARE @ColumnName VARCHAR(128)
 DECLARE @ReferenceObject VARCHAR(128)
 DECLARE @ReferenceColumn VARCHAR(128)
 DECLARE [GetConstraints] CURSOR FOR
  SELECT  Table_name
     ,Constraint_Name
     ,Column_Name
     ,ReferenceObject
     ,ReferenceColumn
  FROM TableConstraints
  WHERE ReferenceObject = @Tablename AND Constraint_Type = 'FOREIGN KEY'
 OPEN [GetConstraints]
 FETCH NEXT FROM [GetConstraints] INTO  @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @SQL NVARCHAR(255)
  IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@ConstraintName)
  AND parent_object_id = OBJECT_ID(@Table))
  BEGIN
  SELECT @SQL =  'ALTER TABLE ' + @Table + '  WITH  CHECK  ADD CONSTRAINT ' + @ConstraintName
      + ' FOREIGN KEY ('+ @ColumnName +') REFERENCES '+ @ReferenceObject
      + ' ('+ @ReferenceColumn +') ON UPDATE  NO ACTION  ON DELETE  NO ACTION'
  END
  --PRINT @SQL
  EXEC    sp_executesql @SQL
  FETCH NEXT FROM [GetConstraints] INTO  @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
 END
 CLOSE  [GetConstraints]
 DEALLOCATE [GetConstraints]

END
  
END
GO
/****** Object:  StoredProcedure [dbo].[spBuildFKConstarints]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author:  Mahadevan
-- Create date: 26/6/2011
-- Description: To rebuild all the FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spBuildFKConstarints]

 @Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;

 BEGIN
 DECLARE @Table  VARCHAR(128)
 DECLARE @ConstraintName VARCHAR(128)
 DECLARE @ColumnName VARCHAR(128)
 DECLARE @ReferenceObject VARCHAR(128)
 DECLARE @ReferenceColumn VARCHAR(128)
 DECLARE [GetConstraints] CURSOR FOR
  SELECT  Table_name
     ,Constraint_Name
     ,Column_Name
     ,ReferenceObject
     ,ReferenceColumn
  FROM TableConstraints
  WHERE Table_Name = @Tablename AND Constraint_Type = 'FOREIGN KEY'
 OPEN [GetConstraints]
 FETCH NEXT FROM [GetConstraints] INTO  @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @SQL NVARCHAR(255)
  IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@ConstraintName)
  AND parent_object_id = OBJECT_ID(@Table))
  BEGIN
  SELECT @SQL =  'ALTER TABLE ' + @Table + '  WITH  CHECK  ADD CONSTRAINT ' + @ConstraintName
      + ' FOREIGN KEY ('+ @ColumnName +') REFERENCES '+ @ReferenceObject
      + ' ('+ @ReferenceColumn +') ON UPDATE  NO ACTION  ON DELETE  NO ACTION'
  END
  --PRINT @SQL
  EXEC    sp_executesql @SQL
  FETCH NEXT FROM [GetConstraints] INTO  @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
 END
 CLOSE  [GetConstraints]
 DEALLOCATE [GetConstraints]

END
  
END
GO
/****** Object:  StoredProcedure [dbo].[spBuildAllIndexes]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author:  Mahadevan
-- Create date: 26/6/2011
-- Description: To create all the indexes in a table
-- =================================================
CREATE PROCEDURE [dbo].[spBuildAllIndexes]

 @Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;

 DECLARE @Table VARCHAR(50)
 DECLARE @IndexName VARCHAR(50)
 DECLARE @Type VARCHAR(50)
 DECLARE @Column1 VARCHAR(50)
 DECLARE @Column2 VARCHAR(50)
 DECLARE @Column3 VARCHAR(50)
 DECLARE @Column4 VARCHAR(50)
 DECLARE @Unique VARCHAR(50)

 DECLARE [GetIndexes] CURSOR FOR
  SELECT T1.Tablename
    ,T1.IndexName
    ,T1.IXDescription
    ,T1.ColumnName AS Column1
    ,T2.ColumnName AS Column2
    ,T3.ColumnName AS Column3
    ,T4.ColumnName AS Column4
    ,T1.IsUnique
  FROM (SELECT RANK() OVER (Order BY ColumnName ASC) AS SNO
     ,ObjectID
     ,Tablename
     ,IndexName
     ,IXDescription
     ,ColumnName
     ,IsUnique
    FROM TableIndex
    WHERE TableName = @Tablename AND IXDescription = 'NONCLUSTERED') T1
  LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
       WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T2 ON T2.ObjectID = T1.ObjectID AND T2.SNO = T1.Sno +1
  LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
       WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T3 ON T3.ObjectID = T2.ObjectID AND T3.SNO = T2.Sno +1
  LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
       WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T4 ON T4.ObjectID = T3.ObjectID AND T4.SNO = T3.Sno +1
  WHERE T1.SNO = 1
 OPEN [GetIndexes]
 FETCH NEXT FROM [GetIndexes] INTO @Table,  @IndexName,  @Type, @Column1, @Column2, @Column3, @Column4, @Unique
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @SQL NVARCHAR(1000)
  IF NOT EXISTS (SELECT * from sys.indexes WHERE Name = (@IndexName)
      AND object_id = OBJECT_ID(@Table))
  BEGIN
  IF (@Column2 IS NULL)
  BEGIN
  SELECT @SQL =  'CREATE ' +@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
      +'(['+@Column1+'] ASC)'
      + 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,'
      + 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
   END
  IF (@Column2 IS NOT NULL AND @Column3 IS NULL)
  BEGIN
  SELECT @SQL =  'CREATE ' +@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
      +'('+@Column1+','+@Column2+')'
      + 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,'
      + 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
   END
   IF (@Column2 IS NOT NULL AND @Column3 IS NOT NULL AND @Column4 IS NULL)
   BEGIN
   SELECT @SQL =  'CREATE ' +@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
      +'('+@Column1+','+@Column2+','+@Column3+')'
      + 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,'
      + 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
   END
   ELSE
   BEGIN
   SELECT @SQL =  'CREATE ' +@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
      +'('+@Column1+','+@Column2+','+@Column3+','+@Column4+')'
      + 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,'
      + 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
   END
   END
     --Print @SQL
  EXEC SP_EXECUTESQL @SQL
  FETCH NEXT FROM [GETIndexes] INTO @Table,  @IndexName,  @Type, @Column1, @Column2, @Column3, @Column4, @Unique
 END
 CLOSE  [GetIndexes]
 DEALLOCATE [GetIndexes] 
  
END
GO
/****** Object:  StoredProcedure [dbo].[spInsertIndexes]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================
-- Author:  Mahadevan
-- Create date: June 22, 2011
-- Description: To insert all indexes used in a database to table Tableindex
-- ====================================================================================
CREATE PROCEDURE [dbo].[spInsertIndexes]

AS
BEGIN

 SET NOCOUNT ON;
    
     TRUNCATE TABLE dbo.Tableindex
    
     BEGIN
  INSERT INTO   [dbo].[TableIndex]
       (ObjectID
       ,TableName
       ,SchemaName
       ,IndexName
       ,IndexID
       ,IndexType
       ,IXDescription
       ,ColumnName
       ,IsUnique
       ,IsUniqueConstraint)
         (SELECT I.OBJECT_ID
     ,T.NAME TABLE_NAME
     ,S.NAME SCHEMA_NAME
     ,I.NAME INDEX_NAME
     ,I.index_id INDEX_ID
     ,I.type INDEX_TYPE
     ,I.type_desc AS IndexDescription
     ,C.NAME COLUMN_NAME  
     ,CASE I.is_unique WHEN 1 Then 'UNIQUE' ELSE '' END AS IS_UNIQUE
     ,CASE I.is_unique_constraint WHEN 1 Then 'UNIQUE CONSTRAINT' ELSE '' END AS IS_UNIQUE_CONSTRAINT
   FROM SYS.TABLES T       
   INNER JOIN SYS.SCHEMAS S     ON T.SCHEMA_ID = S.SCHEMA_ID       
   INNER JOIN SYS.INDEXES I     ON I.OBJECT_ID = T.OBJECT_ID       
   INNER JOIN SYS.INDEX_COLUMNS IC     ON IC.OBJECT_ID = T.OBJECT_ID       
   INNER JOIN SYS.COLUMNS C     ON C.OBJECT_ID  = T.OBJECT_ID   
   AND IC.INDEX_ID    = I.INDEX_ID AND IC.COLUMN_ID = C.COLUMN_ID 
   WHERE 1=1   AND I.type = 2  AND (S.name = 'dbo')
   )
    END
    
   
END
GO
/****** Object:  StoredProcedure [dbo].[spInsertConstraints]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================
-- Author:  Mahadevan
-- Create date: June 22, 2011
-- Description: To insert all constarints used in a database to table TableConstraints
-- ====================================================================================
CREATE PROCEDURE [dbo].[spInsertConstraints]

@Database Varchar(50),
@Schema Varchar(50)

AS
BEGIN

 SET NOCOUNT ON;
    
     TRUNCATE TABLE dbo.TableConstraints
    
     BEGIN
     INSERT INTO dbo.TableConstraints
           (ObjectID
           ,Constraint_Catalog
           ,Constraint_Schema
           ,Constraint_Name
           ,Table_Catalog
           ,Table_Schema
           ,Table_name
           ,Constraint_Type
           ,Is_Deferrable
           ,Initially_Deferrable
           ,Column_Name
           ,Ordinal_Position
           ,TypeDesc
           ,Is_Unique
           ,Is_Unique_Constraint
           ,ParentObjectID
           ,ParentObject
           ,ReferenceObjectID
           ,ReferenceObject
           ,ReferenceColumnID
           ,ReferenceColumn)
     (SELECT Distinct SO.Object_ID
       ,ISTC.Constraint_Catalog
       ,ISTC.Constraint_Schema
       ,ISTC.Constraint_Name
       ,ISTC.Table_Catalog
       ,ISTC.Table_Schema
       ,ISTC.Table_name
       ,ISTC.Constraint_Type
       ,ISTC.Is_Deferrable
       ,ISTC.Initially_Deferred
       ,ISKC.Column_Name
       ,ISKC.Ordinal_Position
       ,SI.type_desc
       ,CASE WHEN SI.is_unique = 1 THEN 'UNIQUE' ELSE '' END
       ,CASE WHEN SI.is_unique_constraint= 1 THEN 'UNIQUE' ELSE '' END
       ,SO.Parent_object_ID
       ,PO.Name AS ParentObject
       ,SFK.referenced_object_id
       ,RO.NAME AS ReferenceObject
       ,FKC.referenced_column_id
       ,SC.name AS Ref_column
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ISTC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ISKC ON ISKC.CONSTRAINT_NAME = ISTC.CONSTRAINT_NAME
  AND ISKC.CONSTRAINT_CATALOG = ISTC.CONSTRAINT_CATALOG
  AND ISKC.TABLE_NAME =ISTC.TABLE_NAME
  INNER JOIN Sys.Objects SO ON SO.Name = ISTC.CONSTRAINT_NAME
  LEFT JOIN Sys.indexes SI ON SI.name = ISTC.CONSTRAINT_NAME
  LEFT JOIN sys.foreign_keys SFK ON SFK.object_id = SO.object_id
  LEFT JOIN (SELECT name, object_id from sys.objects) PO ON PO.OBJECT_ID = SO.Parent_Object_ID
  LEFT JOIN (SELECT name, object_id from sys.objects) RO ON RO.OBJECT_ID = SFK.referenced_object_id
  LEFT JOIN sys.foreign_key_columns FKC ON FKC.referenced_object_id = SFK.referenced_object_id
  LEFT JOIN SYSCOLUMNS SC ON SC.id =  SFK.referenced_object_id AND SC.colid = FKC.referenced_column_id 
  WHERE ISTC.CONSTRAINT_CATALOG = @Database AND ISTC.CONSTRAINT_SCHEMA = @Schema)
    END
    
   
END
GO
/****** Object:  StoredProcedure [dbo].[spDropRefFKs]    Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author:  Mahadevan
-- Create date: 26/6/2011
-- Description: To drop all the Reference FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spDropRefFKs]

 @Tablename VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON;

 BEGIN
 DECLARE @Table  VARCHAR(128)
 DECLARE @ReferenceObject VARCHAR(100)
 DECLARE @ConstraintName VARCHAR(128)
 DECLARE [GetRefs] CURSOR FOR
  SELECT  Table_name,
     ReferenceObject,
     Constraint_Name
  FROM TableConstraints
  WHERE ReferenceObject = @Tablename
  ORDER BY [Table_Name]
 OPEN [GetRefs]
 FETCH NEXT FROM [GetRefs] INTO  @Table, @ReferenceObject, @ConstraintName
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @SQL NVARCHAR(255)
  IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = (@ConstraintName)
  AND referenced_object_id = OBJECT_ID(@ReferenceObject))
  BEGIN
  SELECT @SQL =  'ALTER TABLE ' + @Table + ' DROP CONSTRAINT [' + @ConstraintName + '];'
  END
  --PRINT @SQL
  EXEC    sp_executesql @SQL
  FETCH NEXT FROM [GetRefs] INTO  @Table, @ReferenceObject, @ConstraintName
 END
 CLOSE  [GetRefs]
 DEALLOCATE [GetRefs]

END
  
END
GO

----------------------------------------------
Now design your package as shown below and use appropriate stored procedures in Execute SQL Task.