How to Split the table in SSIS

Posted by Sriharim under Sql Server on 5/20/2015 | Points: 10 | Views : 429 | Status : [Member] | Replies : 4
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

---
Srihari



Responses

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

Up
0
Down
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

Up
0
Down
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

---
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

Up
0
Down
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

Up
0
Down
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).

---
Srihari

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

Login to post response