Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ozsql
Starting Member
14 Posts |
 Posted - 2009-11-09 : 04:18:26
|
| 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 000Record Format Version No Number 1Creation Date/Time Stamp DateTime 20060601013120Region String USTrailer RecordsRecord Type Code String 999Record Format Version No Number 1Creation Date/Time Stamp DateTime 20060601013120Region String USDetailRecordCount Number 35001Control Total Number -2993278934.87Every 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 |
|
|
vincible
Starting Member
6 Posts |
Posted - 2010-05-14 : 15:24:27
|
| Hi,Even i looking for the same solution .Please let me know if you find something.Thanks!!! |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-14 : 15:34:50
|
| This is how we did it in my company.We created a staging table and inserted all our data. Had one more column varchar(xx) [xx is file length]...that will store the header data and another row that stores the trailer data. The remaining columns will not be valued for these two header and trailer rows (apart from the Primary Key of course, with which we find out if its a headr or trailer). We did all this within a stored proc.We then created an SSIS package with 3 separate Data Flow tasks. The first one a "Header Data flow task" that copies the header alone into the Flat File...Followed by the "Detailed Records Task" and "Trailer Record Task". These two tasks should use the same FlatFile connection manager and have the "append" option set to true to append to the existing file. |
|
|
|
|
|
|
Creating Header AND Footer to a flat file destinat