November 9, 2009 at 1:50 am
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
November 9, 2009 at 9:02 pm
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).
November 10, 2009 at 8:28 am
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.
November 10, 2009 at 12:58 pm
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
November 10, 2009 at 1:49 pm
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!
November 10, 2009 at 2:20 pm
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.
September 13, 2010 at 12:19 pm
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 🙂
September 13, 2010 at 12:32 pm
The same technique should work just fine. You'll just want to include carriage returns in your header variable where needed.
September 14, 2010 at 9:30 am
What does it mean by carriage returns...
September 14, 2010 at 9:42 am
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.
September 14, 2010 at 9:53 am
Set your variable up to Evaluate as Expression and use the expression builder to add the carriage returns into your variable.
"This " + "\r is " + "\r a " + "\r test"
September 15, 2010 at 8:56 am
when select [flatfile].[HEADER] for the data flow task.
Then in the expression, I am trying to give CR\LF but the expression doesn't get validated can you please show me an examplE with a screen shot.
Thanks
September 15, 2010 at 9:09 am
September 15, 2010 at 9:21 am
Hitting enter doen't really works
September 15, 2010 at 9:26 am
preensheen (9/15/2010)
Hitting enter doen't really works
Is that your expression?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply