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 12345»»»

Creating Header AND Footer to a flat file destination file Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 1:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 10:53 PM
Points: 27, Visits: 202
Hi Guys,

I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement. (e.g. Check Sum) this requires the source extraction process to include quality checks to ensure that same arrangement is not included in interface files more than once.

Header Record

Fields Type Value

Record TypeCode String 000

Record Format Version No Number 1

Creation Date/Time Stamp DateTime 20060601013120

Region String US



Trailer Records

Record Type Code String 999

Record Format Version No Number 1

Creation Date/Time Stamp DateTime 20060601013120

Region String US

DetailRecordCount Number 35001

Control Total Number -2993278934.87



Every interface file will consist of the following record types:

· Header record – The first record will be the header record.

The purpose of the header is to provide identification information of the file that can be checked by the receving system for assurance that the correct file received. There is only one such record per file and it is the 1st record in the file.



· Detail records – There will be zero or many detail records. They will follow the header record.

· Trailer record – The last record will be the trailer record.

The purpose of the trailer record is to provide identification information of the file and control data that can be checked by the receiving system for assurance that the correct file received and provide a set of batch control total that can be used by the receiving system to check if the content of the file has not been altered since the file was created.



Control Totals: Detail Record count, excluding the header record and trailerrecord.

Control Total 1 = Simple sum of “principle_amount_1” of every detail arrangement.



There is only one such record per file and it is the last record in the file.



I need some help here, how to approach this requirement. Do I need to create separate tables for header and footer with value types.



How to check sum header and footer as per above requirement ?



Any article or code related to above requirements please let me know I will try my best to figure it out.



Management insist me to do it MS SSIS. need some help.


Thanks in Advance,



D
Post #815666
Posted Monday, November 9, 2009 9:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:30 PM
Points: 104, Visits: 317
You could build the header and trailer separately from the detail rows. Then append the files together.

You can write a file as delimited or fixed width with multiple fields. Then read it using a different connection manager as a very long ragged right row with only one field.

Another possibility is to build the output row as one long field as you go. First write the header, then the details (appending to the previous file), then the trailer (appending to the previous file).
Post #816283
Posted Tuesday, November 10, 2009 8:28 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
You can use multiple data flows to write to the same file if you uncheck the "Overwrite file" flag in the Flat File Destination component. So data flow 1 could write the header record, data flow 2 the detail records, data flow 3 the trailer record. That is generally how I construct these multiple record type files.

You could probably use either the derived column or script components to construct the header/trailer data.



Post #816587
Posted Tuesday, November 10, 2009 12:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 942, Visits: 1,742
You can probably get away with just two Data Flow Tasks to generate the file. The first Data Flow Task would generate your Header and Detail records for the file. Then, the second Data Flow Task would be used to write your Footer record to the file. The following may be the steps you would take to develop this in your SSIS package:

1. Place 2 Data Flow Tasks on the Control Flow tab.
2. Use the first Data Flow Task to generate your Header and Detail records (rows)
a. Obtain the data from your data source
b. Transform your data as needed to satisfy the results for your Detail Rows
c. Set up your Flat File Destination in the layout for your Detail Rows. (Don't worry about Header yet)
d. Upon opening your Flat File Destination task you will notice a item for the "Header:". If you were to enter something in the box here it would be hard-coded as the header for you within your file. But, instead of it being hard-coded you can make it dynamic. You can create a SSIS variable to store your dynamically built header information, then assign it to the Header property of the Flat File Destination in the 1st Data Flow task.
3. Set dynamic Header SSIS variable to Header property of Flat File Destination in 1st Data Flow Task.
a. On the Control Flow Tab select the first Data Flow Task then go to the properties window for task
b. Expand [+] Expressions property and click the [...] button
c. In the Property Expressions Editor window click in the left column to bring up a list of properties available within the Data Flow Task for you Header and Detail Rows.
d. Choose the property [Flat File Destination].[Header] (if you haven't renamed it yet) setting the expression to the name of your SSIS variable name you created for your dynamic header.
4. Your dynamic header should now show up when you run the first data flow task with your detail rows.
5. Open up the second data flow and create a source for your footer, I used a script component as an input data source, mapping the source row to an SSIS variable that contained my Footer Row.
6. Create your Flat File Destination for you footer making sure that you set the Overwrite property = False. Your Flat File Destination will be the same file name as your header and detail file name from you first Data Flow Task, but the layout will be different in order to line up with the footer.
7. Upon running you SSIS, the package will run through executing your first Data Flow Task which writes the data from the Header property, then each detail row. Then, it will run the second Data Flow Task writing the footer row to the end of the same file used in your Header and Detail Data Flow Task.

Hope they helps get you started.

Thanks,
John
Post #816767
Posted Tuesday, November 10, 2009 1:49 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,840, Visits: 3,844
Well, I always say I learn something new every day and today is no exception.

The expression builder is not available from the flat-file destination properties window when working inside the data flow. I had no idea that you could set expressions on data flow tasks from the data flow properties at the control flow level.

I've done a great deal of SSIS work, but this one's new to me. Thanks for the insight John!




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #816809
Posted Tuesday, November 10, 2009 2:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 942, Visits: 1,742
John Rowan (11/10/2009)
Well, I always say I learn something new every day and today is no exception.

The expression builder is not available from the flat-file destination properties window when working inside the data flow. I had no idea that you could set expressions on data flow tasks from the data flow properties at the control flow level.

I've done a great deal of SSIS work, but this one's new to me. Thanks for the insight John!


Thanks for the feedback, John R.

I learned this the hard way when I was trying to dynamically set the location of a XSD file on an XML Source (it had a GUI interface for the XML file which could be a variable, but not the XSD). After doing this in one of my packages, I also realized that many of the different properties within the different Data Flow tasks showed up under the Misc. section of the properties window of the Control Flow Task including the transformations I place in a Derived Column Task.

It definitely has been helpful find the various Data Flow tasks properties exposed for expressions on Control Flow.
Post #816843
Posted Monday, September 13, 2010 12:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 7, 2011 1:58 PM
Points: 90, Visits: 221
This really worked out for me. Thanks a lot for the post.
But what if I want 3 lines of header. Say name on first row, time period in second row as an header. And another default(not dynamic) header in the 3rd row.

It would be great help to me if you can tell me the possible way of doing this.

Thanks
Post #985005
Posted Monday, September 13, 2010 12:32 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,840, Visits: 3,844
The same technique should work just fine. You'll just want to include carriage returns in your header variable where needed.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #985013
Posted Tuesday, September 14, 2010 9:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 7, 2011 1:58 PM
Points: 90, Visits: 221
What does it mean by carriage returns...
Post #985620
Posted Tuesday, September 14, 2010 9:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 942, Visits: 1,742
preensheen (9/14/2010)
What does it mean by carriage returns...


It means a new line. (Hitting the enter key)

For example:
My
Header
Info

There would be an unprintable character representing a CR\LF (Carriage Return/Line Feed) {Windows/DOS} after each word.
Post #985626
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse