André Kienitz

In this article I would like to show you how to generate test data with a script component in SSIS. You often construct a data flow in SSIS and want to test it with sample data. Creating appropiate test data can be sometimes very time consuming. Instead of connecting with a data source connection to a database one good way to create test data is using the script component as a source in a data flow.

In this example I show you how to construct mobile numbers when using a script component as a source in SSIS.

Drag a data flow task from the SSIS toolbox to the control flow designer pane. If you want, you can rename it.

aki_image_01

Go to the Data Flow pane and drag the script component from the SSIS toolbox to the data flow designer pane. A pop up window appears where you can select the script component type. Here you choose Source.

aki_image_02

Then you can rename your script component if you want.

aki_image_03

Then open the script component. In the next step you have to define the output columns of the script component. On the left side of the window you click on Inputs and Outputs. Then you click on Output Colums of Output 0. Now you can add columns to the Output 0.

aki_image_04

In this example the first column is called Id with the data type DT_I4. The second column is called MobileNumber. Here the DataType is changed to DT_WSTR.

aki_image_05

Now you choose on the left side Scripts in order to click on the Edit scripts button. A new window opens where you can write some custom code. Add the following lines to the function CreateNewOutputRows:

aki_image_06

The code uses a loop that generates 1000 rows for the output columns Id and MobileNumber. Please note that MobileNumber is a string. The string starts with „491234“. This is concatenated with the current value of i. The zeros behind the colons represent leading zeros. For example for i=17 you have the value 00017.

Now in order to see the data, drag a derived column component from the SSIS toolbox to the data flow designer pane and link them with the script component. Then click on the link and choose data viewer. Your data flow designer pane should look like this:

ask_image_07

Now you can start the SSIS package and the data generated is shown below:

ask_image_08

Now you can develop your data flow task using the generated test data.