Error Handling in SQL Server Integration Services (SSIS) | Part 7

Error Handling in SQL Server Integration Services (SSIS):
Requirement:
1) SQL Server Data Tools for Visual Studio 2015
or
SQL Server Data Tools or Visual Studio 2019
2) and SQL Server instance
3) one CSV file and configure the error out put in a CSV file.
Two way we can handle the error in SSIS
1) At component label
2) Event handler(at package label)
At component label: We can define error handling on each component label and log it in a flat file or in a database.
Error will get generated based upon following any reasons:
i. Auto failed task.
ii. Failed task due to the failure of ancestor’s control.
iii. Truncation happens or source or destination connection issue.
iv. Conversion failure happens.
v. Issues from migrating files and files data.
vi. Package has an error due to privileges of OS controls.
vii. And may be other reasons also.
At Package label: Must ensure to also provide the error handle it packages label. Because you may get the error at any component label so that error will be captured and later you can check the log to resolve the issue.
Some following configuration available for error handling.
Failed Component:
If the data flow task fails, it fails the package. It is the default option.
Ignore failure:
In the case when we change the error mode to ignore failure,
it ignores the error message and completes the execution.
In this case, it will ignore the rows with bad data and continue working with other rows.
It does not throw any error message.
Redirect rows:
It is mostly used. We can redirect the failed rows to a configured destination and view the bad data rows in that file. We can configure it for the truncation or the error message.
CREATE TABLE [Error_log_SSIS](
[PackageID] [nvarchar](50) NULL,
[PackageName] [nvarchar](50) NULL,
[MachineName] [nvarchar](50) NULL,
[TaskName] [nvarchar](50) NULL,
[ErrorCode] [int] NULL,
[ErrorDescription] [nvarchar](max) NULL,
[Created_on] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]