Creating test data with a script component in SSIS

Technical Value

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.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden