- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS - How to Redirect Invalid Rows From Flat File Source [Data Validation]
Let’s say we load data from flat file on daily basis. There is SaleAmount Column that should have only integer (numbers). If somehow the wrong data is provided in that column we do not want to fail the package instead of that we want to redirect those rows to error output.
Different Transformations in SSIS Provide the error output in case the operation cannot be perform on input data. We can redirect error rows from Different sources, transformations and destinations as well. In out example we are using flat file source.
Create connection to text file by using Flat File Connection Manager. I have used below data for initial testing
Change the data type to Integer in Flat File Source ( connection Manager) as Shown below
After choosing the correct data types for the inputs, go to Error Output Table on Left and then configure as shown below
Include two multicast transformation just to test your package with correct data as given above. All the records will go to Success Output as shown below
Let's add one more row to input file with wrong SaleAmount
Mexico,Anthony,test500 and execute the package.
As we can see that the error row is redirect to error output and rest of rows went to success output. SSIS does not provide a column name due to which the row is redirected.Custom Code can be written to find out more details about column name and description. In this example we can go back to our flat file and check the data manually or we can load this file into some staging table and write Tsql queries to validate and find out which row does not have valid data and got rejected in loading process.