SSIS Data load with error handling

Question

Hello guys,

I am new to using SSIS. I am working on a requirement where the client wants me to automate process to load data from 15 different CSV file sources to 15 different SQL tables respectively. I am going with a approach of truncating the table and then using data flow task(flat file source --> Derived columns --> Data Conversion --> OLE DB destination (final table). I have created 15 different packages for those 15 tables in one solution. Here I am struggling with couple of thing.. I would really appreciate some inputs.

1. Client wants a full proof error handling process for all the tables. But the catch is they want a single error SQL table where a summary of the errors that might error are to be loaded into that error table (like which package had error, which row caused the error, which date, what is the error and such) I am finding difficulty in finding a single method of doing it. The methods I found is only capturing if any errors occur at derived column or data conversion step. But I want a process where at any step if there is any error, it has log into that error table but not stop the process.. Basically run the package successfully by loading correct data into SQL tables but log error columns into the error tables and error out.
2. Also, the data load needs to be done on a daily basis. As in take the new csv file, load the old csv file into an archive folder..

Appreciate any inputs.

Answer 1

Hi @Prashanthi EL ,

1) Do you mean you want to capture the error data to another table? If yes, you may Use the Configure Error Output dialog box to configure error handling options for data flow transformations that support an error output.

A simple sample here you may have a reference to error-handling-in-ssis.

2) You may use agent job to run the packages on the schedule.

To move the old file to archive folder you may use File System Task.

ssis-archive-files-and-add-timestamp-to.html

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.