Chapter 7. Unleash the Power of SSIS Script Task and Component

In this chapter, we will cover the following recipes:

• Using variables in SSIS Script task
• Execute complex filesystem operations with the Script task
• Reading data profiling XML results with the Script task
• Correcting data with the Script component
• Validating data using regular expressions in a Script component
• Using the Script component as a source
• Using the Script component as a destination

Introduction

The Script task and Script component allow you to execute a custom Visual Basic or Visual C# code inside your SSIS package control flow or data flow. This way, you can perform complex operations beyond the capabilities of other built-in tasks and transformations. The Script task works like any other task in the control flow. You can use the Script component in the data flow as the source, transformation, or destination.

Both the Script task and the Script component have two design-time modes: you begin editing by specifying properties using the common editors for tasks and components that you are already familiar with, and then switch to a development environment to write the .NET code. The second environment is the Microsoft Visual Studio Tools for Applications (VSTA) environment.

The Script task provides the entire infrastructure for the custom code for you, letting you focus exclusively on the code. You can use any .NET class library and namespace in your code. In addition...

Using variables in SSIS Script task

The objective of this task is to teach you how to work with SSIS variables in a Script task. You will learn two ways, a more complex one and a simpler one. You will typically use the latter in your packages.

Getting ready

There are no special prerequisites for this recipe, except, of course, SSIS 2016. In addition, you can use either your own text files for testing, or the three text files provided with the code for this chapter (Ch07_03.txt, Ch07_08.txt, and Ch07_10.txt).

Note

For your convenience, the VB and C# code snippets needed for this chapter are provided in the Chapter07.txt file.

How to do it...

1. Open SQL Server Data Tools (SSDT) and create a new project using the Integration Services Project template. Place the solution in the C:\SSIS2016Cookbook\Chapter07\Solution folder and name the project AdventureWorksETL.
2. Rename the default package UsingVariables.dtsx.
3. Create a intCounter variable with data type Int32 and a default value of 0.
4. Create a strFile variable...

Execute complex filesystem operations with the Script task

In the previous recipe, you retrieved filenames filtered by name and extension using the Foreach File enumerator of the Foreach Loop container. Sometimes you need more precise filters. For example, you might need to retrieve files with a larger size than a predefined value. You can get the collection of the filenames that satisfy your custom criteria with the Script task.

Getting ready

There are no special prerequisites for this recipe, except, of course, SSIS 2016. In addition, you can use either your own text files for testing, or the three text files provided with the code for this chapter (Ch07_03.txt, Ch07_08.txt, and Ch07_10.txt). Note that the length of the Ch07_03.txt file is 3 bytes, the Ch07_08.txt file is 8 bytes, and the Ch07_10.txt file is 10 bytes.

How to do it...

1. In File Explorer, right-click the Ch07_08.txt file and select Properties, as shown in the following screenshot:

2. Note the file size, 8 bytes. Click OK.
3. In SSDT...

Reading data profiling XML results with the Script task

In this recipe, you will read the XML file produced by the Data Profiling task and use the Script task to read the regular expressions extracted and store them in package variables.

Getting ready

This recipe assumes that you have finished the first recipe of Chapter 5,Dealing with Data Quality, and have the results of the Data Profiling task at your hand.

Note

For your convenience, the results of the Data Profiling task needed for this recipe are provided in the DataProfiling.xml file.

How to do it...

1. Add a new package to the AdventureWorksETL project. Rename the default package RegExValidation.dtsx.
2. Create two package variables. Name them EmailRegEx1 and EmailRegEx2. Use the String data type for both variables.
3. Drag the Script task to your control flow. Rename it ReadPatterns.
4. Open the editor for this task. On the Script page of the Script Task Editor, make sure that the Visual C# language is selected. Add the User::EmailRegEx1 and User::EmailRegEx2...

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...

Validating data using regular expressions in a Script component

In this recipe, you will create a Script Component that will use the regular expressions in the DataProfiling.xml file to validate the emails of the personal data extracted from a flat file.

Getting ready

This recipe assumes that you successfully completed the Reading data profiling XML results with Script task recipe earlier in this chapter. If you did not complete that recipe, you can prepare an appropriate SSIS package simply by completing step 1 of that recipe.

How to do it...

1. Make sure that the RegExValidation.dtsx package is active in the control flow designer.
2. Right-click in the empty canvas of the Connection Managers pane at the bottom of the SSDT window and select New Flat File Connection... from the shortcut menu.
3. Use the Flat File Connection Manager Editor to create a new connection manager, and on the General page enter PersonData into the Connection manager name textbox.
4. To determine the File name property, click Browse...

Using the Script component as a source

In this recipe, you will create a custom source by using the Script Component as a data flow source.

You will connect to a web service and retrieve the data from it, which you will then place into the data flow pipeline.

How to do it...

1. Add a new SSIS package to the AdventureWorksETL project you created at the beginning of this chapter.
2. Change the name of the newly created package to CustomWebServiceSource.dtsx and save it.
3. Make sure the CustomWebServiceSource.dtsx package is active, and then create a new package parameter using the following settings:
   • Property: Value
   • Name: CountryName
   • Data type: String
   • Value: France
4. Drag a Data Flow task to the control flow designer and change its name to Airport Information.
5. Open the Airport Information task in the data flow designer and drag a Script Component to the data flow designer.
6. In the Select Script Component Type dialog, select Source, as shown in the following screenshot:

7. Click on OK to confirm the selection.
8. Change...

Using the Script component as a destination

In this recipe, you will design a custom data flow destination by using the Script Component.

You will use the data retrieved by using the source component created in the Using the Script component as a source recipe and export it in JSON format to one or more files.

The acronym JSON stands for JavaScript Object Notation, an open-source format for representing data in human-readable form that can also be consumed by automated processes.

Getting ready

Before you can complete this recipe, you need to complete the Using the Script component as a source recipe.

How to do it...

1. Make sure that the CustomWebServiceSource.dtsx package of the AdventureWorksETL solution from the C:\SSIS2016Cookbook\Chapter07\Solution\ folder is active in the control flow editor.
2. Add a package parameter using the following settings:
   • Property: Value
   • Name: JSONFilePath
   • Data type: String
   • Value: C:\SSIS2016Cookbook\Chapter07\Files
3. Add another package parameter using the following settings...