How do i process-track record using ssis package and write status log ? ...I am new...please help

Question

Hi All,

I am new in SSIS development. I know basic SSIS and Can Build-Exceute SSIS Packages

Recently I started job where we 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 record for Patient and Doctor info.

I have 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

1. 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 ?

I know the basics, but can't pull it all together, that how do i track each record in SSIS and how do i put back to text file.

Is there is Good Example Or Article ?

Please let me know [email protected]

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

http://stackoverflow.com/questions/142015/ssis-gurus-links-to-great-learning-content-requested

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.