SSIS: Lineage ID

  • Article

This is going to be a simple article that will talk about lineage ID. Each component in the SSIS data flow task has lineage IDs depending on how many columns the data flow processes. Understanding lineage ID will help us understand the buffer allocation in SSIS.

What is Lineage ID?

Lineage ID is a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage ID.

How Lineage ID helps you?

You may want to tune the SSIS package and lineage ID is one of the counters that can help youfine-tunee the package. Transformations in SSIS can be classified as synchronous transformation and asynchronous transformations. The synchronous transformations make use of the buffer allocated by the source adapter and reuse the same, where as asynchronous transformations adds additional buffer. Say the package has 8 transformations and out of that the first 6 are synchronous transformations. If you go and check the lineage IDs for these transformations they would be same for all 6. The last 2 transformations show new lineage IDs and thereby it tells you that new buffer space is used for the transformation.

Synchronous Transformations and Lineage ID

In some cases, synchronous transformations will add new columns. For example, you can add new column in the derived column transformation. In this case, a new buffer is allocated for the new column and also the new lineage ID. You have an option in the derived column to replace the column that would not add new buffer.

When you are developing the package lineage ID is an eye of the buffer and thereby we can understand the transformation behavior and decide the choice of the transformation. There are other easy techniques that will help us understand the data flow behaviors by making use of the SSIS logging but those are not being discussed here now.

See Also: