ssis sql_如何在SSIS中使用SQL随机数

最新推荐文章于 2024-09-14 19:55:29 发布

culuo4781

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量849

点赞数

文章标签：数据库 python java mysql sql

原文链接：https://www.sqlshack.com/how-to-work-with-sql-random-numbers-in-ssis/

版权

ssis sql

介绍 (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.

在本文中，我们将展示如何在SSIS中使用SQL随机数。为了证明这一点，我们将有一个与人相关的表格，并从该列表中随机创建一个赢家。

This example will do the following:

本示例将执行以下操作：

Create a view with the number of rows of the People table from the AdventureWorks database
使用AdventureWorks数据库中的People表的行数创建一个视图
Drop the table with the list of winners if it exists (this list will store the names of the winner selected randomly)
删除包含获奖者列表的表格（如果存在）（此列表将存储随机选择的获奖者的姓名）
Count the number of users of the person list to generate random numbers according to the total number of people
计算人员列表的用户数，以根据人员总数生成随机数
Generate a SQL random number between 1 and the total number of rows in the People table in SSIS
在SSIS的人员表中生成介于1和行总数之间SQL随机数
Finally, store the name of the winner in the Winner table doing a select where row number is equal to the SQL random number
最后，将获奖者的姓名存储在Winner表中，进行选择，其中行号等于SQL随机数

要求 (Requirements)

The following requirements needs to be installed.

需要安装以下要求。

SQL Server installed (any version)
已安装SQL Server（任何版本）
SSDT for Business Intelligence (with SSIS installed) 用于商业智能的SSDT （已安装SSIS）
AdventureWorks database (we will use the person.person table of that database, but you can use any table with some names) AdventureWorks数据库（我们将使用该数据库的person.person表，但您可以使用具有某些名称的任何表）

入门 (Getting started)

The first step will be the following:

第一步如下：

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:

在此步骤中，我们将创建一个名为Adventure数据库的表person.person的vperson的视图。如果您不想安装AdventureWorks数据库，则可以使用另一个表。我们要做的是在这样的视图中添加行号：
```
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.
该视图包括行号（表person.person的行号，名和姓。行号稍后将与SQL随机数进行比较，以选择表Person.Person中的某人。

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:

该表将存储获奖者的名字和姓氏。如果该表存在，此任务将删除它。为此，我们将在SSIS项目的SSDT中使用SQL Execute任务：

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

将“执行SQL任务”拖放到设计窗格中并创建一个新连接：

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.

在SQL语句中，添加以下代码以检测表dbo.winners。如果该表存在，则将其删除。为此，我们将使用OBJECT_ID函数。如果表dbo.winner的OBJECT_ID为NULL，则表示它不存在。另一方面，如果它不为null，则它存在，我们必须删除它。
```
IF OBJECT_ID('dbo.winner') is not null
BEGIN
Drop table dbo.winner
END
```

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.

下一步将计算行数。稍后将使用此信息来生成随机数。例如，如果我们有1000个用户，则SQL随机数将在1到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.

行计数的优点是它可以用于对文本文件，非SQL数据库等中的行进行计数。

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:

在“数据流”中，拖放“ OLED DB源”和“行数”，然后使用箭头加入任务：

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):

双击OLEDB Source，然后选择SQL Server Adventureworks连接，然后选择在本文开头创建的视图（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.

转到菜单，选择SSIS变量并创建countRows变量。使用Int32数据类型。此变量将存储行数。

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:

双击“行计数”任务，然后选择刚刚创建的变量countRows。这会将vperson视图的行数存储到变量中：

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:

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:

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.
在SSIS中的people.people表中生成1到行总数之间的随机数

这是本文最重要的部分。脚本任务生成一个SQL随机数。将脚本任务拖放到设计窗格中：

在SSIS变量中，创建一个Int32数据类型的变量。此变量将存储SQL随机数：

我们将使用Microsoft C＃。您也可以使用Visual Basic（VB）。在ReadWriteVariables属性中添加SSIS变量，然后按Edit Script：

使用的代码如下：

公共无效Main（）
{
- 尝试
  {
  - // TODO：在此处添加您的代码
    bool fireAgain = true;
    随机randint=新的Random（）;
    - Dts.Variables [“ User :: myRandomNumber”]。Number = rand.Next（Convert.ToInt32（Dts.Variables [“ User :: countRows”]。Number））;
      Dts.Events.FireInformation（0，“随机数：” + Dts.Variables [“ User :: myRandomNumber”]。Number.ToString（），String.Empty，String.Empty，0，ref fireAgain）；
      Dts.TaskResult =（int）ScriptResults.Success;
  }
- 抓住（例外）
  {
  - Dts.Events.FireError（18，ex.ToString（），“任务失败”，“”，0）；
    Dts.TaskResult =（int）ScriptResults.Failure;
  }
}
try和catch用于处理错误。如果try内的代码失败，则catch将在输出中引发错误。

bool fireAgain = true; 是fireinformation函数使用的参数。这将在以后触发一条信息消息。

随机randint=新的Random（）;
将用于启动SQL随机数生成器。

下面的代码行将在变量myRandomNumber中存储一个基于变量countRowsSQL随机数，而函数Convert.ToInt32会将变量转换为Integer。在此示例中，由于视图具有19,972行，因此SQL随机数将在1到19,972之间：

Dts.Variables [“ User :: myRandomNumber”]。Number = rand.Next（Convert.ToInt32（Dts.Variables [“ User :: countRows”]。Number））;

另外，该号码将作为参考消息显示在输出中。此步骤是可选的，用于调试目的。

Dts.Events.FireInformation（0，“随机数：” + Dts.Variables [“ User :: myRandomNumber”]。Number.ToString（），String.Empty，String.Empty，0，ref fireAgain）；

另外，该catch用来引发错误。如果catch中的代码失败，则Ex是异常错误，它将显示错误消息详细信息。如果捕获被激活，则Dts.TaskResult将失败。
catch（ex例外） {
- Dts.Events.FireError（18，ex.ToString（），“任务失败”，“”，0）； Dts.TaskResult =（int）ScriptResults.Failure;
}
最后，拖放SQL Execute任务并加入所有任务。看起来应该像这样（可以将任务重命名为更具描述性的任务）：

双击执行SQL任务，然后将连接添加到Adventure Works数据库：

在SQL语句中，添加以下代码：
```
SELECT  LastName, FirstName
 into dbo.winner
 FROM           dbo.vPerson
 WHERE  row#=?
```
该SQL语句将在dbo.winner表中存储姓氏和名字，其中vPerson视图中的行号等于SQL随机数。如您所见，我们正在使用int子句，使用select查询语句的结果创建表dbo.winner。

？用于变量。我们将映射随机数变量，并匹配行号和随机数，并将姓氏和名字存储在dbo.winners表中。

在“参数映射”中，映射myRandomNumber SSIS变量。使用等于0的参数名称和NUMERIC数据类型。这样会映射吗？与变量。

运行程序包并检查dbo.winners表。如果一切正常，您会在表格中看到比赛获胜者的姓名：
```
Select * from dbo.winner
```
每次运行SSIS程序包时，您都会得到一个不同的名称。如您所见，现在您有一项任务随机产生一个赢家。

结论 (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.

在本文中，我们学习了如何检查SQL Server表是否存在以及如何使用SSIS Execute SQL Task删除表。此外，我们还学习了如何计算行数以及如何使用脚本任务在SSIS中生成SQL随机数。

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.

脚本任务和随机数是本文的关键部分。我们使用最佳实践来处理错误（尝试捕获）。我们还在脚本任务中使用了SSIS变量来根据视图的行数生成随机数。

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

最后，我们创建了一个表格，其中包含根据用户数量使用SQL随机数随机选择的比赛获胜者。