Process header and detail record in script task

This is related to Dataflow task in SSIS. I have developed a DFT to process a ragged flat file with header and detail records. 2 different tables to store header(Invoice_header) and detail record(Invoice_lines). These 2 tables are connected with Foreign key relationship and the primary keys are from header record.
All are working perfectly fine with script task and there are 2 flow of records, one for header and another for detail. Header record is first processed and loaded to Invoice_header table and then Invoice_line table is updated with detail record. Now the problem is when there is primary key violation in Invoice_header table, the processing stops for header record but detail records are loaded successfully which is not expected. My requirement is to stop the whole processing when there is any issue in updating the header record that has primary key violation or any issue for that matter.

File Layout:

HDR;SJCSYD;5999920;ACWFNTFLT;08/02/2017;AUD; 112483.26;0;0; 2020700242;SND;DT; 356.13; 2020700242;SND;TL; 194.00; 2020700489;SND;DT; 5503.35; 2020700489;SND;TL; 194.00; HDR;SJCSYD;5999920;ACWFNTFPJ;08/02/2017;AUD; 1242.20;0;0; 6220704503;SND;TL; 83.00; 6220704920;SND;TL; 185.00; 6420700758;SND;TL; 185.00; 6420700761;SND;TL; 83.00;


Flat file source --> Script task --> ole destination 1 for header record --> old destination 2 for detail record.

Please let me know if you need any further details.

more ▼

asked Feb 16, 2017 at 03:09 AM in Default

avatar image

1 2

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 16, 2017 at 03:09 AM

Seen: 29 times

Last Updated: Feb 16, 2017 at 03:10 AM

Copyright 2018 Redgate Software. Privacy Policy