Introduction

In this article, we will show how to work with SQL random numbers in SSIS. To demonstrate this, we will have a table with people and we will create a winner randomly from that list.

This example will do the following:

1. Create a view with the number of rows of the People table from the AdventureWorks database
2. Drop the table with the list of winners if it exists (this list will store the names of the winner selected randomly)
3. Count the number of users of the person list to generate random numbers according to the total number of people
4. Generate a SQL random number between 1 and the total number of rows in the People table in SSIS
5. Finally, store the name of the winner in the Winner table doing a select where row number is equal to the SQL random number

Requirements

The following requirements needs to be installed.

1. SQL Server installed (any version)
2. SSDT for Business Intelligence (with SSIS installed)
3. The AdventureWorks database (we will use the person.person table of that database, but you can use any table with some names)

Getting started

The first step will be the following:

1. Create a view with the number of rows of the People table from the AdventureWorks database.
   
   

   In this step, we are going to create a view named vperson of the table person.person of the Adventure database. You can use another table if you do not want to install the AdventureWorks database. What we are going to do is to add the row number in a view like this:

   1 2 3 4

   create view [dbo].[vperson] as select ROW_NUMBER() OVER(ORDER BY BusinessEntityID ASC) AS Row#,FirstName,LastName   FROM [AdventureWorks2016].[Person].[Person]

   The view includes the row number (row#, first name and last name of the table person.person. The row number will be compared later with the SQL random number to select someone of the table Person.Person.

2. Drop the table with the list of winners if it exists (this list will store the winner selected randomly)
   
   

   This table will store the first name and last name of the winner. If the table exists, this task will delete it. To do it, we will use the SQL Execute task in SSDT in an SSIS project:

   

   Drag and drop the Execute SQL Task to the design pane and create a new connection:

   

   In the SQL Statement, add the following code to detect if the table dbo.winners. If the table exists, it is dropped. To do this we will use the OBJECT_ID function. If the OBJECT_ID of the table dbo.winner is NULL it means that it does not exist. On the other hand, if it is not null, it exists and we must delete it.

   1 2 3 4

   IF OBJECT_ID('dbo.winner') is not null BEGIN Drop table dbo.winner END

   
   

   

3. Count the number of users of the person list to generate random numbers according to the total number of people.
   
   

   The following step will count the number of rows. This information will be used later to generate the random number. For example, if we have 1000 users, the SQL random number will be between 1 and 1000.

   You could use a select count(row#) and store the number, but in this case, we are going to count rows using the row count task in data flow.

   The advantage with row count is that it can be used to count rows in text files, non-sql databases, etc.

   First of all, we are going to drag and drop the Data Flow Task:

   

   Name the Data Flow “Count rows” and double click it.

   In the Data Flow, drag and drop the OLED DB Source and the Row Count and join the tasks with the arrow:

   

   Double click the OLEDB Source and select the SQL Server Adventureworks connection and select the view created at the beginning of the article (dbo.vperson):

   

   Go to the menu and select SSIS variables and create countRows variable. Use the Int32 data type. This variable will store the number of rows.

   

   Double click the Row Count task and select the variable countRows just created. This will store the number of rows of the vperson view into the variable:

   

4. Generate a random number between 1 and the total number of rows in the people.people table in SSIS
   
   

   This is the most important part of the article. The script task to generate a SQL random number. Drag and drop the script task to the design pane:

   

   In the SSIS variables, create a variable with the Int32 Data type. This variable will store the SQL random number:

   

   We will use Microsoft C#. You could also use Visual Basic (VB). Add the SSIS variables in the ReadWriteVariables property and press Edit Script:

   

   The code used will be the following:

   public void Main()
   {
   

   • try
     {
     
     
     • // TODO: Add your code here
       bool fireAgain = true;
       Random rand = new Random();
       
       • Dts.Variables[“User::myRandomNumber”].Number = rand.Next(Convert.ToInt32(Dts.Variables[“User::countRows”].Number));
         Dts.Events.FireInformation(0, “Random number:”+Dts.Variables[“User::myRandomNumber”].Number.ToString(), String.Empty, String.Empty, 0, ref fireAgain);
         Dts.TaskResult = (int)ScriptResults.Success;
     
     }
   
   
   • catch (Exception ex)
     {
     
     • Dts.Events.FireError(18, ex.ToString(), “The task failed”, “”, 0);
       Dts.TaskResult = (int)ScriptResults.Failure;
     
     }
   
   }
   

   The try and catch are used to handle errors. If the code inside the try fails, the catch will throw an error in the output.

   The bool fireAgain = true; is a parameter used by the fireinformation function. This will fire an information message later.

   Random rand = new Random();
   will be used to start the SQL random number generator.

   The following line of code will store in the variable myRandomNumber a SQL random number based on the variable countRows and the function Convert.ToInt32 will convert the variable to Integer. In this example, the SQL random number will be between 1 and 19,972 because the view has 19,972 rows:

   Dts.Variables[“User::myRandomNumber”].Number = rand.Next(Convert.ToInt32(Dts.Variables[“User::countRows”].Number));

   In addition, the number will be displayed in the output as an informational message. This step is optional and is used for debugging purposes.

   Dts.Events.FireInformation(0, “Random number:”+Dts.Variables[“User::myRandomNumber”].Number.ToString(), String.Empty, String.Empty, 0, ref fireAgain);

   Also, the catch is used to fire an error. Ex is the exception error that will display the error message details, if the code inside the catch fails. The Dts.TaskResult will fail if the catch is activated.

   catch (Exception ex) {
   • Dts.Events.FireError(18, ex.ToString(), “The task failed”, “”, 0); Dts.TaskResult = (int)ScriptResults.Failure;
   }

   Finally, drag and drop the SQL Execute task and join all the tasks. It should look like this (optionally rename the tasks to more descriptive tasks):

   

   Double click the Execute SQL Task and add the connection to the Adventure works database:

   

   In the SQL Statement, add the following code:

   1 2 3 4

   SELECT  LastName, FirstName into dbo.winner FROM           dbo.vPerson WHERE  row#=?

   This SQL Statement will store the last name and first name in the table dbo.winner where the row number from the vPerson view is equal to the SQL random number. As you can see, we are using the into clause to create the table dbo.winner with the results of the select query statement.

   The ? is used for variables. We will map the random number variable and match the row# and the random number and store the last name and name into the dbo.winners table.

   In Parameter Mapping, map the myRandomNumber SSIS variable. Use the Parameter Name equal to 0 and NUMERIC Data Type. This will map the ? with the variable.

   

   Run the package and check the dbo.winners table. If everything is fine, you will see the name of the winner of the contest in the table:

   1	Select * from dbo.winner

   
   

   

   Every time that you run the SSIS package you will get a different name. As you can see, now you have a task to generate a winner randomly.

Conclusions

In this article, we learned how to check if a SQL Server tables exists and how drop a table using an SSIS Execute SQL Task. Also, we learned how to count the number of rows and how to generate a SQL random number in SSIS using the script task.

The script task and the random numbers were the key part of this article. We used the best practices to handle errors (try catch). We also used SSIS Variables inside the script task to generate random numbers based on the number of rows of the view.

Finally, we created a table with the winners of a contest selected randomly using the SQL random numbers based on the number of users.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023