• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1069
  • Last Modified:

Loading Flat file with Parent and Child Records using SSIS

Hi I have a Pipe Delimited Flat File(.txt) with data related to orders and order details. This file don't have Header Line. And coming to the data in the File Order records will have 17 columns and Order detail records will have 13 columns. Parent and child records are distinguished as 1) Parent record starts with "ONO" 2) Child record starts with "ODetail" . I have to load this flat file into a sql server table. Please guide me in doing this task. I have attached a file with some sample data in it. Thanks in Advance


HDORD.txt
0
karon1980
Asked:
karon1980
1 Solution
 
rajeevnandanmishraCommented:
You can use different ways:
1. Creating a single table with 17+ columns and then doing "BCP IN" into this table. Later you can write logic to move the data in Order header and in Order detail.
2. Using BCP IN with Format File (Check BCP utility in BOL)
3.  Using SSIS package (if you are not familiar still you will find it easy doing this way).
0
 
karon1980Author Commented:
Hi Rajeev, Thanks for your reply. I am fairly new to SSIS and this should be done in an SSIS DFT and should be automated. Can you please elaborate a bit more on using SSIS to do this. Thanks
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
HI,

are you going to use 2 tables or 1 table for parent and child orders?
Also is it incrmental loads or you'll need to merge records if exists?

Please give more details..the Initial load is so easy by lookign at your file...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
karon1980Author Commented:
I need to load this into a staging table first and then
Move this into a history table.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
OK, so what you're having problem with and what you've done so far?
0
 
Anthony PerkinsCommented:
>>Can you please elaborate a bit more on using SSIS to do this.<<
You need to look into the Split Task component.  This will split the file into two files each having the same number of columns.  You can then import these two files directly into your staging tables for validation and move to your production tables.  One caveat, if the order details record does not have the order number you will need to add it, so that you can subsequently link it back to the order table.
0
 
karon1980Author Commented:
Hi My requirement is that, I should Load this file first into an Staging table and then Load the Orders and Order details seperately into 2 staging tables. What I did so far is to load the Flat file into a staging table by reading it as a Single column and what needs to be done is to load the Orders and Details into the respective tables. This is where I am not getting any idea. I can distinguish the Order and detail rows with Order records starting with ONO and the detail starting with ODetail. But I have a difficulty with Splitting the Pipe delimited row which was read as a single column into 17 columns for orders and 12 columns for details.. Need some help with this...
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Good point, I see why you read it as a single line first, but you don't really have to, and also no need for the staging table,you could do it in one step.

But anyway, let's continue on your work as a single line, use a CONDITIONAL SPLIT TASK to split the orders rows and details rows, then using a script task to split on the PIPE to the respective columns.

This article will help you...
http://consultingblogs.emc.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx

The download link is broken, so I built a package for you using your TEXT file.

Download from here....http://www.box.net/shared/p8v9bpsr3ysp4epg0v7x

Few points on configuring the script task and some other config is attached in the screen shots.

Reagrds,
Jason


split.png
split2.png
split3.png
0
 
karon1980Author Commented:
Thanks a lot Jason, this is what I want exactly..
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Great and welcome to EXPERT EXCHANGE, as I see your sign on date was October 2nd :)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now