Splitting Data from One column into multiple columns using Derived column Transformation SSIS 2012

Hi Guys,

How to derive Data from One column into multiple columns using SSIS?

Inside Dataflow task I will be using derived column transformation task to split the data and then use functions to extrace required data.  SUBSTRING are important functions for our operation.

Source:

Desired Output:

Thats it

Happy Coding.........................................!!!!!!!!!!

Derive Data from One delimited column into multiple columns using SSIS (SQL Server)

Ref:

http://www.everydaysql.com/derive-data-from-one-delimited-column-into-multiple-columns-using-ssis/

How to derive Data from One delimited column into multiple columns using SSIS?
Inside Dataflow task I will be using derived column transformation task to split the data and then use functions to extrace required data. FINDSTRING and SUBSTRING are two important functions for our operation.

In my example I have taken both Source and Destination as OLEDB connections, it can be anything like Flat file, excel, etc.
Source Data: Pipe Delimited

12908|78|E|ABCDEFGH|L|ROMAN|2013-07-29 16:05:29.450
11025|98|D|LMNOPQRS|T|LATIN|2013-07-29 16:05:30.450

Desired Result:


Transformation Tasks used in SSIS data flow task:



Expressions inside Derived Columns:

SUBSTRING(STORE_DATA,1,FINDSTRING(STORE_DATA,”|”,1) – 1)

SUBSTRING(STORE_DATA,FINDSTRING(STORE_DATA,”|”,1) + 1,FINDSTRING(STORE_DATA,”|”,2) – FINDSTRING(STORE_DATA,”|”,1) – 1)

SUBSTRING(STORE_DATA,FINDSTRING(STORE_DATA,”|”,2) + 1,FINDSTRING(STORE_DATA,”|”,3) – FINDSTRING(STORE_DATA,”|”,2) – 1)

SUBSTRING(STORE_DATA,FINDSTRING(STORE_DATA,”|”,3) + 1,FINDSTRING(STORE_DATA,”|”,4) – FINDSTRING(STORE_DATA,”|”,3) – 1)

SUBSTRING(STORE_DATA,FINDSTRING(STORE_DATA,”|”,4) + 1,FINDSTRING(STORE_DATA,”|”,5) – FINDSTRING(STORE_DATA,”|”,4) – 1)

SUBSTRING(STORE_DATA,FINDSTRING(STORE_DATA,”|”,5) + 1,FINDSTRING(STORE_DATA,”|”,6) – FINDSTRING(STORE_DATA,”|”,5) – 1)

RIGHT(STORE_DATA,19)

Syntax:

FINDSTRING (‘Expression’,’Search_string’,’Occurrence’) -> Pointer Result
SUBSTRIING (‘Expression’,’Start_Point’,’Length_of_String’) -> String Result

Convert Non-unicode to Unicode using Data Conversion task


Map the columns accordingly at the destination end.

Introduction:

In this article we are going to see on how to use the Derived Column transformation in SSIS packaging. Derived column transformation is used in cases where we do some manipulations and get the desired result in a separate column. Say for example we need to do some transformations based on calculating the salary with some perks and have it in a separate column then we can go with the derived columns. Let’s jump start to the section on how to do that using a sample package.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use the Derived Columns control. Once you open the project just drag and drop the Derived Column control and a source and destination provider as shown in the below image.

Now we need to do the configuration for each of the tasks, first we will start with Source. In our example we are going to create a table as shown in the below scripts

CREATE TABLE EmpDetails(EMPID int, EMPFName varchar(10), EMPLName varchar(10),

EMPDOB Datetime, EMPSal int, EMPHra int)

GO

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(1,’Karthik’,'Anbu’,’01/01/1980′, 10000,1500)

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(2,’Arun’,'Kumar’,’02/02/1981′, 8000,1200)

INSERT INTO EmpDetails (EMPID, EMPFName, EMPLName, EMPDOB, EMPSal, EMPHra)VALUES(3,’Ram’,'Kumar’,’01/02/1982′, 6000,1000)

Go

Now configure the source to get the details from the table above. Once the source is configured now we need to do the configuration for the destination section. So here we are going to create a new table as shown in the below script

CREATE TABLE EmpDetailsDestination (EmpFullName varchar(21), EmpAge int, EmpCTC int, InsertedDate DATETIME)

Now the records in both the source and destination tables are shown in the below screen

Our primary goal is to do some manipulations using the derived column task and save it in a separate table. So we are configure the Derived Column by double clicking the control will open the window for configuration as shown in the below screen

In the expression section if you see we have created some expressions to do some manipulations as per our requirement.

Now we need to do the configuration for the destination source by mapping the columns as shown in the below screen

Now once all the task steps are configured press F5 to build and execute the package. Once your package is executed your screen looks like below

We can see the output in the destination table as shown below

Conclusion:

So in this article we have seen on how to use the Derived Column Transformation to do some manipulation and transform data to a new column.

- See more at: http://www.f5debug.net/post/2011/04/25/SQL-Server-Integration-Services-(SSIS)-Part-43-Derived-Column-Transformations-in-SSIS.aspx#sthash.yBRUjXdp.dpuf