Step 2: Creating a Corrupted File
Applies To: SQL Server 2016
In order to demonstrate the configuration and handling of transformation errors, you will have to create a sample flat file that when processed causes a component to fail.
In this task, you will create a copy of an existing sample flat file. You will then open the file in Notepad and edit the CurrencyID column to ensure that it will fail to produce a match during the transformations lookup. When the new file is processed, the lookup failure will cause the Currency Key Lookup transformation to fail and therefore fail the rest of the package. After you have created the corrupted sample file, you will run the package to view the package failure.
In Notepad or any other text editor, open the Currency_VEB.txt file.
The sample data is included with the SSIS Lesson packages. To download the sample data and the lesson packages, do the following.
Navigate to Integration Services Product Samples.
Click the DOWNLOADS tab.
Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file.
Use the text editor's find and replace feature to find all instances of VEB and replace them with BAD.
In the same folder as the other sample data files, save the modified file as Currency_BAD.txt.
Make sure that Currency_BAD.txt is saved the same folder as the other sample data files.
Close your text editor.
On the Debug menu, click Start Debugging.
On the third iteration of the data flow, the Lookup Currency Key transformation tries to process the Currency_BAD.txt file, and the transformation will fail. The failure of the transformation will cause the whole package to fail.
On the Debug menu, click Stop Debugging.
On the design surface, click the Execution Results tab.
Browse through the log and verify that the following unhandled error occurred:
[Lookup Currency Key] Error: Row yielded no match during lookup.
The number 27 is the ID of the component. This value is assigned when you build the data flow, and the value in your package may be different.