How do I process-track record using ssis package and write status log?

Question

We are importing-exporting data from-to text file to sql server 2005 db using SSIS.

All my text files are too big with lots of Pharmaceutical records for Patient and Doctor info.

I have a job to import using ssis, apply some processing in SSIS and Log Error.

For Example :

1. Read DOCTOR.TXT File (7000 Records) With DoctorId, Date,Name... Fields

2. SSIS I need to track where Name="SAM" and Date is NULL

3.Tracked record need to write to error log.txt file

4. Also need to create separate Txt file where SSIS execution detail is logged:

   • SSIS Package Name
   • Execution Date
   • Status
   • Any Other SSIS Package Execution Audit Detail

How do I do this using .Net code or any other way to do this in SSIS or Sql Server?

How do I track each record in SSIS and how do I put back to text file.

Is there is good example or article?

Answer 1

This is a good book to read:

http://www.amazon.com/Microsoft%C2%AE-Server-2005-Integration-Services/dp/0735624054

Here are some resources from SO

What are the recommended learning material for SSIS?

Answer 2

If you break this down, it is a simple process:

1. Use a Flat File source and a File Connection object to read in data from the input text file
2. Use a Conditional Split on the output of the Flat File source to split records based upon your condition (i.e. Name="SAM" and Date is NULL) In a Conditional Split this would look like: Name == "Sam" && ISNULL( Date )
3. Use the Conditional Split output for your error condition to a Flat File Destination and a second File Connection to write out your errors.
4. Process the remaining data as needed and write to whatever destination you want using the default Conditional Split output.
5. On the main Control Flow Surface, right click and choose logging. This will give you several options on how you want to log your execution.

All of this being said, there is much truth to the other answer, read a lot and try it out in SSIS.