Correcting data with the Script component

In this recipe, you will use the Script Component in the data flow as a transformation for advanced data cleansing. You will read an Excel file and do a custom transformation in order to make the output ready for further processing in the data flow.

Getting ready

In order to test this recipe, you need to have an Excel file prepared. In the file, there should be a single sheet with the following content:

Note

For your convenience, an Excel file with the content needed is provided in the Ch07_Orders.xls file.

Note that the table represents simple orders with order details. However, the order info is added to the first order details line only. Your task is to add the appropriate order info to every single line.

How to do it...

1. Add a new package to the AdventureWorksETL project. Rename it ProcessingExcel.dtsx.
2. Add a new package-level Excel connection manager. Rename it Excel_Ch07_Orders. Point to your Excel file path and define your Excel version appropriately...