Processing flat file with Header,Detail and Trailer

Question

Processing flat file with Header,Detail and Trailer

naresh0407

Old Hand

Points: 362
More actions
April 4, 2014 at 10:15 pm

#285309

Hi All,
I have below flatfile.Please let me know how to process this file without scripttask in SSIS.
I need to split header into 1 file or db,detail into another file and trailer into another filee.
I have tried with condition split its not working.
H,2011-06-02
D,value1,value2,value3,1
D,value1,value2,value3,2
D,value1,value2,value3,3
D,value1,value2,value3,4
T,4, 10
Thanks in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

naresh0407 (4/4/2014)
Hi All,
I have below flatfile.Please let me know how to process this file without scripttask in SSIS.
I need to split header into 1 file or db,detail into another file and trailer into another filee.
I have tried with condition split its not working.
H,2011-06-02
D,value1,value2,value3,1
D,value1,value2,value3,2
D,value1,value2,value3,3
D,value1,value2,value3,4
T,4, 10
Thanks in Advance

Do you mean that you actually need to create 3 DISK files from that one file? Or are you trying to put the data into 3 different tables?

Also, are you actually trying to do this in SSIS and just trying to avoid the script task or are you trying to avoid SSIS altogether?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

naresh0407 Old Hand Points: 362 More actions · Answer 2

Thank you for the reply..

Basically i have Header and DetailRow table seperalty.So need to transfer H row to header table and D row to DetailRow.

Can i acheve this without scriptask?

I have one table for header which will store the header date,detail table to store the actual data and trailer can be moved to text file or table.I will count the detail records and store the count in variable using rowcounnt tranformation and later i need to match this count with trailer row count ie.4

H,2011-06-02

D,value1,value2,value3,1

D,value1,value2,value3,2

D,value1,value2,value3,3

D,value1,value2,value3,4

T,4, 10

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Drat... I was hoping you wanted a T-SQL-only solution. 😛 I don't actually know how to spell SSIS so I can't help here. Hopefully, someone who knows SSIS will be along soon. I'll watch and learn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

naresh0407 (4/5/2014)
Basically i have Header and DetailRow table seperalty.So need to transfer H row to header table and D row to DetailRow.
Can i acheve this without scriptask?
I have one table for header which will store the header date,detail table to store the actual data and trailer can be moved to text file or table.I will count the detail records and store the count in variable using rowcounnt tranformation and later i need to match this count with trailer row count ie.4
H,2011-06-02
D,value1,value2,value3,1
D,value1,value2,value3,2
D,value1,value2,value3,3
D,value1,value2,value3,4
T,4, 10

First question, why not use a script task?

There are few options, on a 2005, most would be reading the file line by line and somewhere along the way parse the line into column and insert in a table. The parsing can happen in a script task, derived column task or in the database. Before one can advice on which is the best method, some more details are needed, file sizes, frequency, number of fields/columns and so on.

The easiest and most straight forward way of doing this in SSIS (spell checked :hehe: ), is to load the files straight into a table, one row per line and then use DelimitedSplit8K[/url] to parse it into the destination tables. Or use a script task 😎

naresh0407 Old Hand Points: 362 More actions · Answer 5

Hi..Actually are asked to validate this task without scripttask.We should use inbuilt transformations to achieve this task.Could you please tell me how can we acheive this.Just let me know the flow.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

Eirikur Eiriksson

SSC Guru

Points: 182881

April 6, 2014 at 12:14 am

#1703565

What version of SQL Server / SSIS are you using?

naresh0407 Old Hand Points: 362 More actions · Answer 7

naresh0407

Old Hand

Points: 362

April 6, 2014 at 3:02 am

#1703571

Hi..I am using sqlserver 2008

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 8

naresh0407 (4/6/2014)
Hi..I am using sqlserver 2008

In Sql Server 2008 the support for multi document type / multi format document files is very limited and some workarounds are needed. Without using a script task, even more so.

1. Set up a Flat File source and configure it to read each line as one column.

2. Use a derived column with an expression to extract the type identifier for the row.

3. Pass the flow to a Conditional Split with one output for each type.

4. Write each to a Raw File destination.

5. Using each Raw File as a source, parse the data.

😎

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

naresh0407 (4/5/2014)
Hi..Actually are asked to validate this task without scripttask.We should use inbuilt transformations to achieve this task.Could you please tell me how can we acheive this.Just let me know the flow.

Is this a homework problem? I ask because it's difficult for me to understand why someone would place such restrictions on a production project.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 10

Jeff Moden (4/6/2014)
naresh0407 (4/5/2014)
Hi..Actually are asked to validate this task without scripttask.We should use inbuilt transformations to achieve this task.Could you please tell me how can we acheive this.Just let me know the flow.
Is this a homework problem? I ask because it's difficult for me to understand why someone would place such restrictions on a production project.

If it is, it does raise some questions.

😎