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

SSIS - Import flat file with trailer Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 8:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:18 AM
Points: 17, Visits: 60
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
Posted Thursday, September 27, 2012 9:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:28 AM
Points: 579, Visits: 914
apache626 (9/27/2012)
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


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/
Post #1365589
Posted Monday, October 1, 2012 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739
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
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1366674
Posted Monday, October 1, 2012 12:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
This might be useful.

http://www.bidn.com/articles/integration-services/234/handling-complex-text-files-in-ssis
Post #1366700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse