Hello again friends! In today’s post we will see 5 SSIS tricks to work with the Microsoft SQL Server Integration Services tool, it is a platform for creating business solutions for data transformation and integration. Integration Services can extract and transform data from various sources such as XML data files, flat files, and relational data sources, and then load the data to one or more destinations.
1. How to make conditional decisions in a package
The lack of a decision task for control flow may seem strange. However, there is a workaround that allows us to achieve the same effect as we would with a “decision task”.
The precedence control between tasks can be based on an expression. Constructing the provenance control as an expression allows us to construct an execution decision.
– To achieve the result of an if/else, we may need two provenance constraints with opposite expressions constructed.
– You can use a sequence container to ensure that the tasks in the if / else path will complete before the following tasks continue.
5 SSIS tricks
2 How to map expressions in a data stream
Data flow tasks do not have the Expressions property to parameterize task properties. This doesn’t mean we can’t parameterize properties, we just have to do it in a different way.
Instead of looking for Expressions in the data flow task, we should look for them in the control flow task. The Expressions configuration in the control flow task will show us the properties of each task within the data flow, allowing us to configure expressions for all properties.
5 SSIS tricks
3. How to use multiple tasks in one package part
Package parts are a great way to reuse parts of the ETL process, but they have a limitation: each package part can have only one control flow task.
There is an interesting and easy workaround for this: if we use a container task, like a sequence container, this will still be a single task, but inside the container we can add multiple control flow tasks.
SSIS
5 SSIS tricks
4. How to use a variable in the tasks of a package part
All the variables we create in a package part will have the scope of one of the tasks it contains.
Package parts do not have anything similar to a package level variable. What if we need to share values between many tasks in the package part?
The solution is simple, related to the previous problem: Create the variables using the stream container scope we mentioned earlier. This way they will be available to all tasks in the sequence container.
5. How to assign the package part connection manager to the package connection manager
Package parts cannot use connection managers in the solution, they need to have their own connection managers.
When we embed 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 configure all connections to use this parameter, including the package part. This way we avoid duplicating the connection string everywhere.
We hope it will help you. If you need help with administration or derivatives. Do not hesitate to contact us. More information about SSIS at https://docs.microsoft.com/es-es/sql/integration-services/sql-server-integration-services?view=sql-server-ver15
Do not forget to subscribe to our Newsletter and take a walk through our blog: h ttps://www.gpsos.es