Lets Suppose When we are extracting data from Source and loading into destination, getting some errors due to unexpected data in SSIS.
SSIS provide a powerful feature Error handling which is used to handle the errors.
Error Reasons might be as following:
1) Data Conversion errors.
2) Expression Calculation errors.
There are three types of error output to handle the error .
1) Fail Component: If there is an error, then data flow task will fail.
2) Ignore Failure: Error is ignored.
3) Redirect Rows: It returns failed rows to failed output. It will not fail the task.
Now we are explaining to configure Error Handling in SSIS using example.
Here, We are using the “Example” CSV file for Source file and data present in”Example” CSV file.
Steps to handle the Error in SSIS:
Step 1 : Drag and Drop “Data Flow Task” From SSIS Toolbox to Control Flow Tab.
Step 2 : Double click on Data Flow Task, Drag and Drop Flat File Source , Data Conversion and Ole DB Destination from SSIS Toolbox to Data Flow Tab.
Step 3 : Double Click on Flat File Source, Flat File Source Editor window will open.
Step 4 : Click on “New ” button ,Flat File Connection manager Editor window will open to browse the “Example” CSV File for Extracting the data and click on columns button to verify the columns.
Step 5 : Click on “Columns ” button to check the columns mapping.
Step 6 : Click on “Data Conversion“ to configure error output. Here we have selected option “Redirect Row” to load failure rows into other destination and Change data type of “Type“ column as Integer.
Step 7 : Double click on OLE DB destination, OLE DB Destination Editor window will open to load success data of CSV File.
Here, We are creating the table in which we will load successful data.
CREATE TABLE [Success Data] (
[Id] varchar(50),
[ Name] varchar(50),
[ Amount] varchar(50),
[Copy of Type] int
)
Step 8 : Click on “Mapping” button to verify the columns.
Step 9 : Drag and Drop OLE DB Destination 1 from SSIS Toolbox to Data Flow Tab and Drag and drop the RED arrow to OLE DB Destination 1.
Step 10 : Double click on OLE DB destination 1, OLE DB Destination Editor window will open to load Failure data of CSV File.
Here, We are creating the table in which we will load Failure data.
CREATE TABLE [Failure Data] (
[Id] varchar(50),
[ Name] varchar(50),
[ Amount] varchar(50),
[Type] varchar(50),
[ErrorCode] int,
[ErrorColumn] int
)
Step 11 : Click on “Mapping” button to verify the columns.
Step 12 : Click on “Ok” button and Execute the Package.Here we can see the Three records are Loaded in Success Destination and Five in Failure Destination.
Step 13 : Open SSMS to check the data in “Failure Data” and “Success Data” Tables.
If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.
Clients will find it using our advanced search filter and will contact you directly.
No any charge for the product lsiting.