5 SSIS tricks you would like to know

How to make conditional decisions in a package

It may seems strange the lack of a decision task for the control flow. However, there is a work around to allow us achieve the same effect we would have with a “decision task”.

The precedence control between tasks can be based on an expression. Building the precedence control as an expression allow us to build an execution decision.

Some details about the implementation are important:

• To achieve the result of an if/else we may need two precedence constraint with opposite expressions built.
• You can use a sequence container to ensure the tasks on the if/else path will be completed before the following tasks continue.

How to map expressions in a Data flow

Data flow tasks don’t have the Expressions property to parameterize the properties of the task. This doesn’t mean we can’t parameterize the properties, we just need to do it in a different way.

Instead of looking for Expressions in the data flow task, we should look for it in the control flow task. The Expressions configuration in the control flow task will show to us the properties of every task inside the data flow, allowing us to configure expressions to all the properties.

 

How to use multiple tasks in a package part

Package parts are a great way to reuse pieces of the ETL process, but they have a limitation: Each package part can have a single control flow task.

There is an interesting and easy work-around for this: If we use a container task, such as a sequence container, this will still be a single task, but inside the container we will be able to add multiple control flow tasks.

 

 

How to use a variable across tasks in a package part

All the variables we create in a package part will have the scope of one of the tasks in it.

Package parts don’t have anything similar to a package level variable. What happens if we need to share values across many tasks in the package part.

The solution is simple, related to the previous problem: Create the variables using the scope of the sequence container we mentioned before. In this way, they will be available to all task in the sequence container.

 

How to map the package part connection manager to the package connection manager

Package parts can’t use the connection managers in the solution, they need to have their own connection managers.

When we insert the package part inside a package we can configure its connection manager and set the connection string.

We need to create a project parameter with the connection string and set all connections to use this parameter, including the package part. In this way we avoid duplicating the connection string everywhere.

The post 5 SSIS tricks you would like to know appeared first on Simple Talk.

from Simple Talk https://ift.tt/3mHWdJK
via