imani_technology (6/30/2014)

---

We are trying to extract flat files into a staging database. The extracted data from the flat files should be "raw" in the Kimball sense. That means there shouldn't be any changes to the data as they are being extracted. The staging tables should have the exact format as the source flat files and there should be a table for each type of flat file.

The boss wants an additional table in the staging database: a data dictionary. There should be data flow task that populates the data dictionary table the same time as the staging tables are populated. I have copied and pasted the script that creates the data dictionary table below.

CREATE TABLE [dbo].[DataDictionary](

[ExtractDate] [datetime] NULL,

[TblName] [varchar](65) NOT NULL,

[ColName] [varchar](50) NOT NULL,

[MacroName] [varchar](30) NULL,

[LongName] [varchar](65) NULL,

[Description] [varchar](500) NULL,

[PKPos] [smallint] NOT NULL,

[Datatype] [varchar](50) NULL,

[Length] [int] NULL,

[Prec] [smallint] NULL,

[Scale] [smallint] NULL,

[IsNullable] [bit] NOT NULL,

[IsIdentity] [bit] NOT NULL,

[DefaultValue] [varchar](100) NULL,

[Active] [bit] NOT NULL,

[CreatedByObj] [varchar](50) NULL,

[IgnoreOnCompare] [char](1) NOT NULL,

[ChangeHashBytes] [varbinary](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Here are my questions: 1) do we need this data dictionary table in our staging database that is basically the flat files in tables? 2) Why or why not?