1

I have a CSV file which I need to load into a SQL database. The issue is, that i need to split some Data into different tables. During the load I need to make sure, that when the first part is loaded into the first table I get that ID which will be put into the foreign key field in the second table when I try to load the other data in the database.

How do I load the data into multiple tables with the data coming from CSV file by maintaining the data integrity?

  • Is a .Net application an option for you? I do data conversion work and sometimes I find it easier to manipulate the data in a method rather in sql. – Mark Kram Feb 06 '13 at 13:03

1 Answers1

0

-- My source file

Part1,Part2
A,a
B,b
C,c

-- Steps

  1. Create a temp table

    CREATE TABLE [dbo].[Part1And2]( [RowID] [int] IDENTITY (1,1) NOT NULL, [Part1] varchar NULL, [Part2] varchar NULL ) ON [PRIMARY]

  2. truncate this table using Exceute SQL Task

  3. DFT 1 : Load the file into a temp table. Now Part1 and Part2 sets of fields get coupled. Source : Your source file Destination : The table you created.

  4. DFT 2 : Split the data Source : The temp table you just loaded Feed it to multicast tsk Create two outputs from the multicast and connect them to the individual tables (Say Part1 and Part2) Select the fields for each table In our example, table Part1 will have two fields RowID and Part1 table Part2 : RowID and Part2

This is just the starting point. Think what would you do when you get the next set of file. How would the IDs be assigned?

Anoop Verma
  • 1,495
  • 14
  • 19