How to Split the table in SSIS

Posted by Sriharim under Sql Server on 5/20/2015 | Points: 10 | Views : 1191 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have below data in a single flat file.
Source data:


ID  Name  Salary    Address
1   aaa    25000    Pune
2  bbbb    30000     Bangalore

After loading data using flat file source. I have to split the data into 3 different tables, like below tables


ID   Name
1    aaa

ID   Salary
1    25000

ID   Adress
1    City

In a single Data Flow Task, how can i split the data (with out using multicast task) into 3 different tables, is there any way ?

Is it Possible using Script Component ? if yes, how it is ?

please suggest

Mark as Answer if its helpful to you
---
Srihari

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 5/20/2015 [Member] [MVP] Platinum | Points: 25

0	Hi, I am able to fulfill this reuirement by using Data Flow Task, 3 Falt File Sources and 3 OLE DB Destination tasks.... 1. Drag Data Flow Task onto SSIS Working Area in the Control flow 2. Go to Data Flow area 3. Take 3 Flat File Sources 3.a. Open Flat File Source 1 Editor --> In the Columns left pane, Map only the Id and Name Columns 3.b. Open Flat File Source 2 Editor --> In the Columns left pane, Map only the Id and Salary Columns 3.c. Open Flat File Source 3 Editor --> In the Columns left pane, Map only the Id and Address Columns 4. Take 3 OLE DB Destinations and map Flat file sources to each destination by mapping input & output columns.... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

I am able to fulfill this reuirement by using Data Flow Task, 3 Falt File Sources and 3 OLE DB Destination tasks....

1. Drag Data Flow Task onto SSIS Working Area in the Control flow
2. Go to Data Flow area
3. Take 3 Flat File Sources
3.a. Open Flat File Source 1 Editor --> In the Columns left pane, Map only the Id and Name Columns
3.b. Open Flat File Source 2 Editor --> In the Columns left pane, Map only the Id and Salary Columns
3.c. Open Flat File Source 3 Editor --> In the Columns left pane, Map only the Id and Address Columns
4. Take 3 OLE DB Destinations and map Flat file sources to each destination by mapping input & output columns....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/20/2015 [Member] Starter | Points: 25

0	is it possible by taking a single flat file source and then split/copy the loaded data into 3 different tables ? i need to do using single flat file source Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 5/20/2015 [Member] [MVP] Platinum | Points: 25

0	1. Take Data Flow Task .... Load Flat File data into one staging table named as SplitTableIntoMultiple create table SplitTableIntoMultiple(ID int,Name varchar(100),Salary int, Address varchar(100)) 2. Take Execute SQL Task in the Control Flow and move staging table data to destination tables as follows: INSERT INTO SplitTable1 SELECT Id, Name FROM dbo.SplitTableIntoMultiple INSERT INTO SplitTable2 SELECT Id, Salary FROM dbo.SplitTableIntoMultiple INSERT INTO SplitTable3 SELECT Id, Address FROM dbo.SplitTableIntoMultiple NOTE: If you want to drop staging table at the end of Package execution, you can do... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

1. Take Data Flow Task .... Load Flat File data into one staging table named as SplitTableIntoMultiple

create table SplitTableIntoMultiple(ID  int,Name  varchar(100),Salary   int, Address varchar(100))

2. Take Execute SQL Task in the Control Flow and move staging table data to destination tables as follows:

INSERT INTO SplitTable1 
SELECT Id, Name FROM dbo.SplitTableIntoMultiple

INSERT INTO SplitTable2 
SELECT Id, Salary FROM dbo.SplitTableIntoMultiple

INSERT INTO SplitTable3 
SELECT Id, Address FROM dbo.SplitTableIntoMultiple

NOTE: If you want to drop staging table at the end of Package execution, you can do...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/20/2015 [Member] Starter | Points: 25

0	but, I was asked to do that work in a single Data Flow Task (with single flat file source for loading data and with out multicast). Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts