Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Processing flat file with Header,Detail and Trailer Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 10:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:17 PM
Points: 8, 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
Post #1558695
Posted Saturday, April 5, 2014 11:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:20 PM
Points: 35,552, Visits: 32,148
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558752
Posted Saturday, April 5, 2014 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:17 PM
Points: 8, 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
Post #1558762
Posted Saturday, April 5, 2014 1:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:20 PM
Points: 35,552, Visits: 32,148
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558774
Posted Saturday, April 5, 2014 3:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 PM
Points: 2,394, Visits: 6,638
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 ), 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

Post #1558794
Posted Saturday, April 5, 2014 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:17 PM
Points: 8, 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.
Post #1558812
Posted Sunday, April 6, 2014 12:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 PM
Points: 2,394, Visits: 6,638
What version of SQL Server / SSIS are you using?
Post #1558819
Posted Sunday, April 6, 2014 3:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:17 PM
Points: 8, Visits: 30
Hi..I am using sqlserver 2008
Post #1558825
Posted Sunday, April 6, 2014 3:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 PM
Points: 2,394, Visits: 6,638
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.



Post #1558827
Posted Sunday, April 6, 2014 9:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:20 PM
Points: 35,552, Visits: 32,148
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse