Conditional Split Data Transformation in SSIS


This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from SQL Server database table to multiple flat files. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from SQL Server database table to multiple flat files. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Source Table:-


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emp](
[EMPLOYEE_ID] [int] IDENTITY(1,1) NOT NULL,
[FIRST_NAME] [nchar](20) NULL,
[LAST_NAME] [nchar](20) NULL,
[SALARY] [money] NULL,
[JOINING_DATE] [date] NULL,
[DEPARTMENT] [nchar](20) NULL,
[REGION] [nchar](10) NULL,
CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED
(
[EMPLOYEE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Emp] ON

GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (1, N'John', N'Abraham', 1000000.0000, CAST(0x94360B00 AS Date), N'Banking', N'South')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (2, N'Michael', N'Clarke', 800000.0000, CAST(0x94360B00 AS Date), N'Insurance', N'South')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (3, N'Roy', N'Thomas', 750000.0000, CAST(0xB3360B00 AS Date), N'Banking', N'North')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (4, N'Tom', N'Jose', 600000.0000, CAST(0xB3360B00 AS Date), N'Insurance', N'North')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (5, N'Jerry', N'Pinto', 650000.0000, CAST(0xB3360B00 AS Date), N'Insurance', N'East')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (6, N'Philip', N'Mathew', 750000.0000, CAST(0x94360B00 AS Date), N'Services', N'South')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (7, N'Cook', N'Adams', 650000.0000, CAST(0x94360B00 AS Date), N'Services', N'East')
GO
INSERT [dbo].[Emp] ([EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT], [REGION]) VALUES (8, N'Ian', N'L%', 88.0000, CAST(0xB3360B00 AS Date), N'Insurance', N'West')
GO
SET IDENTITY_INSERT [dbo].[Emp] OFF
GO


0

Destination Files:-
1. Insurance.txt
2. Servcies.txt
3. Banking.txt


Conditional Split Data Transformation
The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.

1. From the Toolbox Double-click or drag the two "OLB DB SOURCE" component to the Data Flow Task.

2. We first need to select one of the connection managers we created earlier. From the OLE DB connection manager drop-down list, select the connection manager you created to the "Test" database. On my system, the name of the connection manager is "LocalHost.Test". Next, select the "dbo.Emp" table from the Name of the table or the view drop-down list. The OLE DB Source Editor should now look similar to the one shown in below image

1

2

3. If you see the "Department" column in above screenshot, it is showing Department of Employees is Banking, Insurance and Services. Now add a conditional split transformation to the data Flow pane and provide the output of Flat File source as input to Conditional Split transformation.

4. From the Toolbox Double-click or drag the "Conditional Split" component to the Data Flow Task.

5. Double-click on the Conditional Split transformation to open the Conditional Split Transformation Editor. Provide proper OUTPUT Name as shown below

For filtering Banking ? Department == "Banking"
For filtering Insurance ? Department == "Insurance"
For filtering Services ? Department == "Services"

3

6. Now we need to add three Flat File Destination components and we can attach the conditional task output to them using the green arrow line. Whenever you drag and drop on destination then it will ask for WHICH output we need to set as source as shown below and select the correct one for each destination.

4

7. After configuring the flat file connection, the package will display as shown in below

5

8. Now execute the package and see the output.

6

9. You can see the data moved to the flat files by applying the condition given

7

Refer the attached sample output flat files.
Banking
Services
Insurance


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: