SSIS package No data issue

Question

Hi I have created basic SSIS package that reads data from Flat Txt file using comma separated and inputs into MS SQL database. Package is working alright but when there is no data in the flat file Then it displays message "No records found." when ever flat file has this message my package stops working as column mapping distracts. Any idea to solve this issue.

Note: Flat file is generated by some automatic tool I cant change it.

Sample File:

====================== Here is output ================

You can see Both Lookup Match and No Lookup are running.

Answer 1

you can add a data flow to count the records before the main data flow. Execute main only if there are more than one record on the flat file. The control flow would look like this:

Answer 2

Keep a separate flow when an error occurs and log into flat file

Refer to this for more details:

http://sqlknowledgebank.blogspot.com/2013/04/ssis-data-flow-error-handling.html

It is similar to exception handling in any of the programming lanaguages.

If the exception is unhandled , then package terminates abruptly.

Inorder to avoid that, we need to catch the exception and log it .

This avoids abrupt stopping of packages and it exeutes sucessfully.

Answer 3

I just need to update status of all the records which are not in the flat file so I used OLEDB command on the top of my package and updated status of all records in the table. Rest I kept my package as it is(without any changes).