September 1, 2014

SQL SERVER: SSIS - Conditional Split Data Flow Transformation Task

Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups.

1. Create sample text file:
This text file is piped delimited and last row  in text Total Row count

EmployeeNumber|Employeename
242|Lorem ipsum
239|dolor sit
225|amet consectetur
242|adipisicing elit
142|seddo eiusmod
222|tempor incididunt
142|ut labore
143|dolore magna
144|Ut enim
Total Row count|9


2. Create sample table:
Create sample destination table in Test database
  1: CREATE TABLE [dbo].[Employee](
  2:  [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
  3:  [EmployeeNumber] [int] NULL,
  4:  [EmployeeName] [varchar](50) NULL,
  5:  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
  6:  (
  7:   [EmployeeId] ASC
  8:  )
  9: )
 10: GO

3. Add Data Flow Task:
Add data flow task in Package for transfer data source text file “Employee.txt” to sql server table “dbo.Employee”
SQLYoga.com 
4. Add new Flat File Connection:
Right click on Connection Manager and add new Flat File connection
SQLYoga.com 
5. Set Flat file source properties:
Flat file source properties set Connection manager Name, File Name (Source full filepath), column names in the first data row as a true
SQLYoga.com
6. Add Flat file source:
Add “Flat File Source” in “Data flow Task” and set properties Flat file connection manager and retain null values from the source as null values in the data flow as true and click button on preview and see in this file extra row include in text file
SQLYoga.com
7. Add Conditional Split Transformation in Data Flow Task:
Add Conditional Split Transformation in data flow task for split row
SQLYoga.com
8. Set Condition Split Properties:
Set properties Condition split where EmployeeNumber equal to “Total Row count” then this rows not use and other row use to process.
SQLYoga.com
9. Add new OLE DB Connection:
Right click on Connection Managers and add new OLE DB Connection
SQLYoga.com 
10. Set OLE DB Connection properties
OLE DB Connection properties set Server Name, Connection to a database and click on ok button
SQLYoga.com 
11. Add OLE DB Destination:
Add “OLE DB Destination” in “Data flow Task” and set properties OLE DB Connection Manager and Name of the table or the view
SQLYoga.com
12. Set Input output selection :
Set Input output selection between Condition spit and OLE DB Destination
SQLYoga.com
SQLYoga.com
13. Run package and Check :
SQLYoga.com
SQLYoga.com

In this way we get split the information and use as per the requirement,

Reference: Tejas Shah (www.SQLYoga.com)

No comments:

Post a Comment