Create a Configuration Table

Before you begin

Import the SSIS package into SSMS.

Procedure

  1. Use the following code to create the PackageDetails table that you want to execute from the main package. This table stores the details of the Integration Services (SSIS) package. The following piece of code also creates the Logs table. 
    Use [<database name>]
GO
 
CREATE TABLE [dbo].[PackageDetails](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [packageName] [varchar](225) NULL,
    [packageFolderPath] [varchar](1024) NULL,
    [isActive] [int] NULL
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[Logs](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [step] [varchar](300) NULL,
    [packageName] [varchar](200) NULL,
    [logged_At] [datetime] DEFAULT getdate() NULL
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[FileLoadInformation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](100) NULL,
    [RecordCount] [int] NULL,
    [CreatedOn] [datetime] DEFAULT getdate() NULL
) ON [PRIMARY]
GO
  2. Use the following code to insert the package details: 
    USE [<database name>]
GO
 
DECLARE @MyPackageFolderPath varchar(1000);
SET @MyPackageFolderPath = '<path to the package>';
 
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Activity_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Alarm_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('AlarmAttributeValue_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('AppVersion_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('AttributeValuesHistory.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Calculation_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Calculation_Input_Data_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Calculation_Input_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Calculation_Instance_Dependencies_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Characteristic_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Client_Connection_User_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Container_Location_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Crew_Schedule_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Defect_Details_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Department_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Dependency_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('EquipmentPropertyHistory.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Component_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Configuration_Data_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Configuration_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Configuration_Value_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Container_Data_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_Detail_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Event_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('EventHistory.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Events_Xref_Lot_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Local_PG_Line_Status_history.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('NonProductive_Detail_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PDF_Process_Segment_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Input_Event_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Input_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Output_Event_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Path_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Path_Unit_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PrdExec_Path_Unit_Starts_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_Component_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_Dependency_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_Equipment_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_Family_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Process_Segment_Parameter_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Prod_Line_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Prod_Unit_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Definition_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Definition_Property_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Definition_Property_Value_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Dependency_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Dependency_Version_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Family_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Location_History.dtsx', @MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Properties_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Segment_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Product_Segment_Parameter_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Plan_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Plan_Starts_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Setup_Detail_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Setup_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Setup_Starts_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Production_Starts_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('PU_Group_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('S95_Event_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Segment_Parameter_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Sheet_Column_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Site_Parameter_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Specification_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Table_Fields_Values_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Test_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Timed_Event_Detail_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Timed_Event_Fault_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Timed_Event_Summary_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('User_Defined_Event_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('User_history.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('User_Parameter_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Variable_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Waste_Event_Detail_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Waste_Event_Fault_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Web_Report_History.dtsx',@MyPackageFolderPath,1)
INSERT INTO [dbo].[PackageDetails] ([packageName],[packageFolderPath],[isActive]) VALUES('Web_Report_Instance_History.dtsx',@MyPackageFolderPath,1)
GO

What to do next

Execute the SSIS package.