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