The Table structure is very simple and it fulfils a number of roles,

CREATE TABLE [dbo].[ImportTableList]

(

[ImportTableListId] INT NOT NULL IDENTITY (1,1) NOT FOR REPLICATION,

[SourceSystem] NVARCHAR(100) NOT NULL,

[ProcessStream] TINYINT NOT NULL,

[SchemaName] NVARCHAR(50) NOT NULL,

[TableName] NVARCHAR(100) NOT NULL,

[LoadPackageName] NVARCHAR(100) NOT NULL,

[IsTableActive] BIT NOT NULL CONSTRAINT [DF_ImportTableList_IsTableActive] DEFAULT 0,

CONSTRAINT [PK_ImportTableList] PRIMARY KEY ([ImportTableListId]),

)

I changed the SourceSystem to an NVARCHAR(100) for simplicity, but it could be split out into a table with an FK if required.

The key columns for the Builder are SchemaName, TableName, PackageName and IsTableActive

The SchemaName refers to the Schema on the source, while TableName is used by Both source and destination.

PackageName is the name that the is called though I use a generic format and so don't use it for anything other than the ETL control process.

IsTableActive is used both by the Builder to identify packages that need to be built tables, as well as the ETL process to identify which tables are to be loaded.

It has a few issues, especially with non-standard characters (eg %,£,$,?) in field names, when creating the package.

In regards to Data I cant provide that as its unique to every system, but a row may look like

INSERT INTO ImportTableList

(

SourceSystem

,ProcessStream

,SchemaName

,TableName

,LoadPackageName

,IsTableActive

)

VALUES

(

'MySource'

, 0

, 'dbo'

, 'aTable'

, 'Staging.MySource.dbo_aTable.dtsx'

, 1

)

NOTE : Both the source Table and Destination table need to be exactly the same definition.