Updated March 31, 2023
Definition of SSIS Conditional Split
SSIS conditional split is one of the major components in MSBI tools. That is Microsoft Business Intelligent tools. It is implied at the time of data flow in the ETL process. The conditional split is the program used by the developer to make the program or process executed by using simple conditions which is easy to understand and it can be easy like CASE statement or IF statement to perform any specific task. In this article, we can see the conditional split implementation in SQL Server integration systems.
What is an SSIS conditional split?
The conditional split in SSIS is simple like Case statements or IF conditions. It ensures that the given condition is dependent on the output of the condition, the result will be directed to the suitable destination path, it has a single input and multiple outputs. For example, if we want to save the student database in a table by the ranking system, then a conditional split can be implemented by splitting the data with the given condition. Then its structures and processes the data and gives the final output using the given condition. But the conditional split in SSIS is case-sensitive.
Create SSIS conditional split
The conditional split transformation can be used to check the executed condition and manage the data flow to the given destination based on the executed condition. For example, if the age of a man is above 58, he can avail senior citizen quota. If not, he is not considered a senior citizen. Then, in this case, the IF statement is used to structure and split the data.
The above example can be considered as a condition used to create a conditional split. The senior citizen is given a quota. So we can create a list accordingly first.
1. Create an excel file with details like person id, person name, person age, person grade.
2. Then from the Dataware house create a SQL table to save, extract and filter the data from the source of the excel. A person below the age of 58 is not considered a senior citizen and the above will go into the senior citizen quota.
3. Click on the SSIS project and there choose control flow to drag and drop the data flow task.
4. Give the typical name and double click to navigate to the data flow window.
5. The source file in excel is retrieved from the toolbox of SSIS
6. Then configure the excel source to hold the excel file which is part of the ETL process.
7. The component conditional split is dragged and dropped from the toolbox of SSIS
8. Then right-click and edit the two conditions to split the result into two varied formats according to the age group, one is below 58 and the other above 58.
9. Once, the queries are added to the page, click on OK and save the work done.
10. To include the split data in the database, choose the ADO.NET destination from the SSIS component to get the output in two different formats and it needs to be adjusted separately. So we need to add two components of ADO.NET, then configure it accordingly.
11. Execute the project and verify the output in the SQL table.
SSIS Conditional Split Transformation
The execution of the conditional split transformation is the same as the CASE statement in the programming. It computes the queries based on the result and navigates the data to the desired output. The transformation can be applied to get the default output also so that if any row suits the no expression, the default result is printed.
The expression should be provided to compute the Boolean function for every condition, to test where the transformation needs to be executed. Then the order where the conditions are processed should be specified. Then it should be evaluated and the row is fed to the output and it should be processed according to the given conditions to check if true or not.
Only one output is achieved from one input and if it is the first output according to the first condition then it can be set to true. If the condition denotes any values in the column of the first name then it should begin with A to a single output and the rows should be initialized with the letter B, if we get different output. Then it should be processed accordingly and should be fed to A or B.
SSIS conditional split Implementation
Integration services can come with operators and functions which the user needs to compile queries to evaluate the input and also manage the direct output data. The conditional split transformation has the customized property called Friendly Expression with the loaded packages. The transformation has a single input and the output can be single or multiple and sometimes it gets single error output. The advanced editor property is also used to fetch the properties which can be configured programmatically.
Split Control
To achieve a control flow, there is no conditional split required. The same functions are attained by including expressions or queries to the precedence constraints or values. Then if it is double-clicked between the precedence value and task, in the editor dialog box the expression values can be added which helps to compute whether it is true or false. There is zero expression builder required to this modal window and it can be copied all the data from memory. So the user should cautious here, as the expression field is prone to case-sensitive. So it is preferred to include a blank script with the unique name before the precedence constraints to act as an effective conditional split.
Conclusion
Hence it is a brief process about the implementation and configuration of the conditional split function. The concept is simple, by using the conditional statements the raw data can be split into two data set and can be used for the required purpose.
Recommended Articles
This is a guide to SSIS Conditional Split. Here we discuss the Definition, What is an SSIS conditional split, examples with code implementation. You may also have a look at the following articles to learn more –
