What happens when incorrect data type is selected in SSIS?
When we compare anything in SSIS then it should be of same data type. When data come from CSV is with “string” data type. So it has to be converted. For example consider the below image example where no error is seen. But error is with data type in customer amount. Customer amount fills numeric value. If it’s not numeric. If customer amount is string than this comparison is not possible. When connection managers connect to source or destination they define data type automatically which is by default “string”.
To solve the error
We need to do some type of data conversion. And for that we have component called “Data conversion” in SSIS. Drag and drop“Data Conversion” componentfrom SSIS toolbox on the “data flow” design page. Follow the screen shot.
When you click on the “Edit” Data Conversion Transformation Editor window will come. When you Select the “Available input Columns” that is “CustomerAmount”. After that we need to select the “Data type” that is “Currency[DT_CY]” and also select the Input Column& Output Alias. The following screen.
When you to conditional split and try to put, numeric value is greater than 0. He is not able to do that. It is not possible because SSIS has own data type.
When you drag & drop the “Conditional Split” to “ADO NET Destination” this popup windows will come. It shows in the below screen shot.
How to handle the error using SSIS component?
Data coming from CSV file may contain records inappropriate records like negative value, non-numeric value. In this example we have one such CSV file which is having such flaws. When we trying to convert the “CustomerAmount” to currency data type.If anyone put the value in “CustomerAmount” is non-numeric value, then it showsfollowing error.
We need to do errorhandling in the “Data Conversion Customer Amount” component so that program do not halt and rows which is with error goes directly into error component. For that from SSIS toolbox we need to go to Destination and drag & drop the “Flat File Destination” on the data flow pane.
When the error happens during the Customer Amount conversion, when the error happens there was no any movement further application stop. So the by default, behaviour is that the application fails or stops right there.
To see this behaviour then right click on the “Data Conversion Customer Amount” and select “Edit”. The following windows come…
after that click in the “Configure Error Output”. Now we can see the default error is happen than this component will fail. The following windows will come.
Fail Component: - This is the default option set for all components within SSIS and whenever error is occurred when Fail Component is set Data Flow will stop down immediately.
Redirect Rows: - This option when set does not stop Data Flow and its output can be redirected and can be given as an input to another component to handle it separately.
Ignore Failure: - When this option is set then as the name suggest error is ignored and data row is directed to the output.
Truncation: - Some of the situation in an application trite truncation as an error or some of the application don’t trite as error. In truncation given all the three option.
When we apply “Configure Error Output” you will see yellow symbol. It says that row which are send to the error output will be loss. All the rows containing error will go to red data line which will be further connected to Flat File Destination.
Now drag & drop “Flat File Destination” from SSIS toolbox and rename that to “Flat File Destination Conversion error row” file on Data flow pane. Then connect output of “Data Conversion Customer Amount”component as input to “Flat file Destination Conversion error row”. Then configure the “Flat File Destination Conversion error row” and redirect the row than apply. The following screen shot...
Any kind of data conversion redirect the “Flat File Destination Conversion error row”. Then configure the “Flat File Destination Conversion error row”. See the following screen shot…
When you configure the “Flat File Destination Conversion error row” then you will see the “Flat File Destination Editor” screen. Over here click on “New” which will open one more window to select Flat File Format, here select “Delimited” and click “OK”. The following screen shot will come…
When you click on “Browse” you will see one window which will create one “Text document” file with “delimited” type and this file is containing the “ErrorConversion” then open “ErrorConversion” file.The follow the screen shot…
So we have finished up configuring error handling mechanism now when you run the package it will run it completely and occurred error will go error component and save it in the error “delimited” text file.
Also get to see following video on MSBI project series which start from the basic to start SSIS : -