How to work with random numbers in SSIS

SSIS General How To
1

Introduction

In data integration projects, random number generation is often required for various purposes—such as creating test data, assigning random IDs, or generating sample datasets. The FUN_RANDOM_INT function, available in ZappySys SSIS PowerPack, is a versatile tool that generates random integers directly within an SSIS package.

This article will demonstrate, along with examples, how to use FUN_RANDOM_INT to generate random numbers in SSIS for various use cases.

Example Scenario

Let’s consider an example where we must generate random customer IDs between 1 and 9999. The derived column expression for this would look like:

<<9999,FUN_RANDOM_INT>>

This generates a random integer in the range of 1 to 9999.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from here.

Steps

Step 1: Create a Variable

  1. First, create a type int64 variable in SSIS. This will store the random number generated using FUN_RANDOM_INT.

Step 2: Generate a Random Number

  1. In the part of your SSIS package where you need to use a random number, use the following syntax to generate a random integer between 1 and the specified maximum value:
<<max_number,FUN_RANDOM_INT>>

For example, if you want a random number between 1 and 2147483647 (Max range):

<<2147483647,FUN_RANDOM_INT>>

Alternatively, if you want to store the random number in a variable (e.g., User::MySSISVar1), use:

{{User::MySSISVar1,FUN_RANDOM_INT}}

Step 3: Use the Random Number in ZappySys Components

  1. Drag and drop a ZappySys component such as REST API Task, JSON Source, or XML Destination to utilize the random number in your data workflow.

Step 4: Example with REST API Task

  1. Let’s take an example where you’re using the REST API Task to fetch data from an API, and you want to generate a random number as part of the query. Set the variable or use the expression inside the component configuration to create the number dynamically.

    REST API Task
    REST API Task838×735 47.4 KB

Step 5: Example with XML Destination

  1. Similarly, you can use the XML Destination to load random data into an XML file, utilizing the FUN_RANDOM_INT function to generate unique IDs for each record.

    XML Destination
    XML Destination744×731 47.2 KB

Conclusion

Using the FUN_RANDOM_INT function in ZappySys SSIS PowerPack simplifies the generation of random integers in SSIS workflows. Whether you need random numbers for data testing, sample generation, or assigning unique IDs, this function provides an efficient and flexible solution.

If you need further assistance or encounter any issues, don’t hesitate to contact the ZappySys support team.

References