SSIS package inset header and detail

Question

No | Name
121 | test1
245 | test3
546 | test4

I have got a text file same as above , i want to insert name and extension this file in to table1 after that i want insert contents file with id from table1 in to the table 2(like header and detail). table1 and table2 have a relation with id.

CREATE TABLE dbo.Table_1
(
Id int NOT NULL IDENTITY (1, 1),
filename varchar(50) NULL,
extention varchar(50) NULL
)  ON [PRIMARY]

CREATE TABLE dbo.Table_2
(
Id int NOT NULL IDENTITY (1, 1),
Table1_Id int ,
No varchar(50) NULL,
name varchar(50) NULL
)  ON [PRIMARY]

I want use SSIS pakage.how to do that? please help

Answer 1

Assume that you have multiple file at one location but format is same for all file and file name is different.

1) Use ForEatch Loop Editor --- Collection >> Enumerator chose Foreach File Enumerator and Specify folder location >> File type *.txt

Variable Mapping >> Chose your local variable like (filename) and Index set 0

Add control inside foreach container

2) Use Execute SQL Task Editor and insert record into table_1 and set @Identity value into new variable call ‘ID’ ( Which generate from table_1) 3) Now you have ID column of Table_1 you can insert that value in Table_2 4) Use Derived Column and Add additional column for table_2 is call ‘ID’

let me know if you need more clarification