SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Processing flat file with Header,Detail and Trailer


Processing flat file with Header,Detail and Trailer

Author
Message
naresh0407
naresh0407
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 30
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218556 Visits: 41996
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
naresh0407
naresh0407
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 30
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
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218556 Visits: 41996
Drat... I was hoping you wanted a T-SQL-only solution. :-P 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41304 Visits: 19504
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 to parse it into the destination tables. Or use a script task Cool
naresh0407
naresh0407
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 30
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
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41304 Visits: 19504
What version of SQL Server / SSIS are you using?
naresh0407
naresh0407
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 30
Hi..I am using sqlserver 2008
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41304 Visits: 19504
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.

Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218556 Visits: 41996
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search