Home > ETL Best Practices > Metadata – What Is It (2)? – ETL-Generated Metadata

Metadata – What Is It (2)? – ETL-Generated Metadata

ETL-Generated Metadata

 

As ETL physical processes are built, we need to generate specific metadata to capture the inner workings of each process. According to the Toolkit book, ETL metadata falls into four main categories:

image

image

I am going to deviate from the terms used above. In the ETL-generated metadata, I will use the following 4 terms. The primary reason is that we use SQL Server and the Integration Services as our implementation platform. It makes sense to use terms that are familiar to SQL developers.

Task

This is the physical task in the smallest scope that implements the data mapping. It generally represents a task in the control flow of a SSIS package.

Package

Tasks are contained in a package. Package can also be referred as Batches. Normally this is a SSIS package.

SQL Job

This is the physical SQL job created in the SQL Job Agent. Each job can contain several packages.

Job Schedule

This is the scheduling of each SQL job.

If you implement our ETL solution without a dedicated tool, it does not mean that you are excused from generating the metadata. Tools are meant to ease the burden of these tasks. Without the tool, we will have to produce and maintain metadata manually. They should be kept in a version-control system such as TFS.

See next blog “Metadata – What Is It (3)? – ETL Package/Task Metadata”.

Categories: ETL Best Practices
  1. sandraSH
    December 2, 2014 at 8:52 pm

    How you implement this in ssis ???

    Like

  1. September 23, 2010 at 4:57 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: