[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do i split a csv file with header and detail lines using SSIS into 2 output files or sql tables?

Posted on 2009-04-17
9
Medium Priority
?
945 Views
Last Modified: 2013-11-10
I need to take a csv file with header and detail records and import them into 2 sql tables.  Right now i'm trying to use SSIS by creating a flat file source that creates one column and then conditional split it based on the beginning of the row field that starts with either HDR or LIN.  The conditional split is not working for me.  In conditional split i'm using SUBSTRING(OneLine,1,3)=="HDR" but it's not returning an output column.
0
Comment
Question by:Bama_Smitty
  • 4
  • 3
  • 2
9 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24168033
could you attach the CSV file?
Do you want to split the csv file to insert data in a SQL Table?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:Bama_Smitty
ID: 24168117
yes i want to split the file to insert the header records in one table and the detail records in the another table.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24168134
ok... could you attach here an example file?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:Bama_Smitty
ID: 24168319
Here is the file sample
PO-SAMPLE.txt
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24168347
HDR is the header and LIN the detail?
0
 

Author Comment

by:Bama_Smitty
ID: 24168551
that's correct
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 24172638
Use a Data Flow Task. Split the file into two separate files. To do this, use Flat File Source and select your file that contains the HDR and LIN rows.  Connect the Source to a Conditional Split.
Set the Source file up as Ragged Right. It will bring each line in as 1 row. Column 0. Make sure you set the length of the row to 4000 or something large enough to hold each row. The default is only 50 so you will need to change i.
In the ConditonalSplit, set the Output to HDR and the Condition to:
SUBSTRING([Column 0],1,3) == "HDR"
For the Default output, type in LIN.
You will two destinations, both Flat File Destinations. When you connect the Conditional Split tot he File Destinations, choose HDR for one and LIN for the other.
In each Destination will need to bring in Colum0 as a Row. Create new file connection managers, each Ragged Right.
Viola, you have two files - one HDR and one LIN. Now load each file into a table, each with it's own column definitions.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24172663
Here is the Data Flow picture:

split-file.jpg
0
 

Author Comment

by:Bama_Smitty
ID: 24184338
Worked beautifully.  Thanks for your help.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question