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

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.
Bama_SmittyAsked:
Who is Participating?
 
HoggZillaCommented:
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
 
PedroCGDCommented:
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
 
Bama_SmittyAuthor Commented:
yes i want to split the file to insert the header records in one table and the detail records in the another table.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
PedroCGDCommented:
ok... could you attach here an example file?
0
 
Bama_SmittyAuthor Commented:
Here is the file sample
PO-SAMPLE.txt
0
 
PedroCGDCommented:
HDR is the header and LIN the detail?
0
 
Bama_SmittyAuthor Commented:
that's correct
0
 
HoggZillaCommented:
Here is the Data Flow picture:

split-file.jpg
0
 
Bama_SmittyAuthor Commented:
Worked beautifully.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.