SSIS - Import flat file with trailer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS - Import flat file with trailer

SSIS - Import flat file with trailer

Rate Topic

Display Mode

Topic Options

Author

Message

apache626

Posted Thursday, September 27, 2012 8:02 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41

I have a flat file with trailers that the total the amounts in certain columns. I need to import the trailer into a different table. How can I split the data so that I can put the trailer data into a different table. Here is the sample data below:

1002A Mickey Mouse 500.00 25.00 ACMECORP 1000.00
1002B Duck Donald 125.00 15.00 ACMECORP 2500.00
1002T Doe Jane 750.00 20.00 ACMECORP 2300.00
1002 1375.00 60.00 5800.00 2010
1003 6740.56 75.00 7800.23 2011

Post #1365580

rhythmk

Posted Thursday, September 27, 2012 9:36 PM

Old Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603

apache626 (9/27/2012)

Try using conditional split.

However please provide more input for more assistance e.g.
1. What is the trailer part in above example ?
2. Is there any fixed pattern or length for this ?
3. Logic behind trailer generation in more detail.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Smile

Post #1365589

Tim Mitchell

Posted Monday, October 01, 2012 11:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

Agreed that the simplest way is to use a conditional split if possible. However, if your footer record(s) is not structured like your other rows, it's possible that you could get an error during processing, or worse, the footer row(s) would actually be processed with your data. In cases such as that, you may have to use a couple of derived column transformations to handle the data, or possibly a script component to programmatically filter out those rows.

This is one area where SQL Server 2012 offers a distinct advantage - it won't blow up if you have files with dissimilarly structured rows. If you're on 2012, the conditional split design pattern for filtering header/footer rows is usually your best bet.

hth,
Tim

Tim Mitchell
SQL Server MVP
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #1366674

Daniel Bowlin

Posted Monday, October 01, 2012 12:32 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673, Visits: 2,418

This might be useful.

http://www.bidn.com/articles/integration-services/234/handling-complex-text-files-in-ssis

Post #1366700

« Prev Topic | Next Topic »

Permissions