FlatFile Source in SSIS

Question

In My Flat File source, i want to transfer all these data in OLDEDB. But I want to DIVIDE data into different tables.

Example.
Table one starts in first %F and ends before another %F in col[0].
And table two starts in second %F with different header because it has different fields than the first table.

Is this possible in SSIS?

Answer 1

Looks like, in a single flat file, 2 table data are provided. From image, it looks like, both tables have different data structure also. I think, it is difficult to load the file at one step.

May be, this steps will hep you.

Step 1. Load all the data into a table (Let to a table named [Table]). Load including the column headers.Data may look like this (just a pattern as example.) In this table make sure you add an increment column

Step 2. A query like below will help you identifying from which row does the 2nd table starts.

Select Top 1 Column0 From [Table] Where Column1 = '%F' Order By Column0 Desc

In your ssis package, add a variable to store above result

Step 3. Add a dft with source as [Table]. After the source add a conditional split.

If Column0 < variable value, sent row to [Table1]
else to [Table2]

There may be some more modifications, still.

---

Added as per comment:

If you have more than 1 table.

step 1. Load all data to one table.

step 2. Add an additional column ([columnX] in image). Its value should be in such a way that, with it you should be able to identify the table.

step 3. Use a conditional split itself, using columnX map each rows to its corresponding table.

---

As per request, added Edit: use a logic like this..Run the script in SSMS and see the result.

Declare @table table (id int identity(1,1),Col1 varchar(5), ColX int)
Insert into @table (Col1) Values
('%F'),('%R'),('%R'),('%R'),('%R'),('%R'),('%R'),
('%F'),('%R'),('%R'),('%R'),('%R'),('%R'),('%R'),
('%F'),('%R'),('%R'),('%R'),('%R')

Select  *
from    @table A

Update  Y
Set     ColX = Z.X
From    @table Y Join(
Select  A.id FromId,B.id ToId,A.X  From 
(
Select id,ROW_NUMBER() Over (Order By id) X From (
Select id from @table Where Col1 = '%F'
Union
Select max(id) id From @table ) Lu ) A,
(
Select id,ROW_NUMBER() Over (Order By id) X From (
Select id from @table Where Col1 = '%F'
Union
Select max(id) id From @table ) Lu ) B
Where A.X = B.X - 1 ) Z On Y.id >= Z.FromId and Y.id < Z.ToId
Select  *
from    @table A

Select  *
from    @table A