SSIS 使用 .CSV 文件 SQL Server 2005 中的参数执行存储过程 [英] SSIS Execute a Stored Procedure with the parameters from .CSV file SQL Server 2005

问题描述

我正在学习 SSIS，这似乎是一项简单的任务，但我被卡住了.

I'm learning SSIS and this seems like an easy task but I'm stuck.

我有一个包含此数据的 CSV 文件 Orders.csv:

I have a CSV file Orders.csv with this data:

ProductId,Quantity,CustomerId
1,1,104
2,1,105
3,2,106

我还有一个存储过程 ssis_createorder 作为输入参数:@productid 整数@数量整数@customerid 内部

I also have a stored procedure ssis_createorder that takes as input parameters: @productid int @quantity int @customerid int

我想要做的是创建一个 SSIS 包，该包将 .csv 文件作为输入，并为 .csv 文件中的每一行调用 ssis_createorder 三次(第一行包含列名).

What I want to do is create an SSIS package that takes the .csv file as input and calls ssis_createorder three times for each row in the .csv file (the first row contains column names).

这是我到目前为止所做的.

Here is what I have done so far.

我创建了一个 SSIS 包(Visual Studio 2005 和 SQL Server 2005).

I have created an SSIS package (Visual Studio 2005 & SQL Server 2005).

在控制流中，我有一个数据流任务.

In Control Flow I have a Data Flow Task.

数据流具有我的 .csv 文件的平面文件源.所有的列都被映射.

The Data Flow has a Flat File source of my .csv file. All of of the columns are mapped.

我创建了一个名为 orders 的对象类型变量.我还有变量 CustomerId、ProductId 和 &int32 类型的数量.

I have created a variable named orders of type Object. I also have variables CustomerId, ProductId, & Quantity of type int32.

接下来，我有一个 Recordset Destination，它将 .csv 文件的内容分配到 varialbe 订单中.我不确定如何使用这个工具.我将 VariableName(在 Customer Properties 下)设置为 User::orders.我认为现在订单包含由原始 .csv 文件中的内容组成的 ADO 记录集.

Next I have a Recordset Destination that is assigning the contents of the .csv file into the varialbe orders. I'm not sure about how to use this tool. I'm setting the VariableName (under Customer Properties) to User::orders. I think that now orders holds an ADO record set made up of the contents from the original .csv file.

接下来，我将在控制流标签上添加一个 ForEach 循环容器并将其链接到数据流任务.

Next I'm adding a ForEach Loop Container on the Control Flow tag and linking it to the Data Flow Task.

在 ForEach 循环容器内部，我将 Enumerator 设置为ForEach ADO Enumerator".我将ADO 对象源变量"设置为 User::orders".对于枚举模式，我选择第一个表中的行".

Inside of the ForEach Loop Container I'm setting the Enumerator to "ForEach ADO Enumerator". I'm setting "ADO object source variable" to User::orders". For Enumeration mode I'm selecting "Rows in the first table".

在变量映射选项卡中，我有 User::ProductId 索引 0、User::Quantity 索引 1、User::CustomerId 索引 2.我不确定这是否正确.

In the Variable Mapping tab I have User::ProductId index 0, User::Quantity index 1, User::CustomerId index 2. I'm not sure if this is correct.

接下来，我在 ForEach 循环容器内有一个脚本任务.

Next I have a Script Task inside of the ForEach Loop Container.

我将 ReadOnlyVariables 设置为 ProductId.

I have ReadOnlyVariables set to ProductId.

在 Main 方法中，这就是我正在做的:

In the Main method this is what I'm doing:

 Dim sProductId As String = Dts.Variables("ProductId").Value.ToString

 MsgBox("sProductId")

当我运行程序包时，我的 ForEach 循环容器变为亮红色，并收到以下错误消息

When I run the package my ForEach Loop Container turns Bright Red and I get the following error messages

Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::ProductId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::ProductId" cannot be applied.
Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::Quantity" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::Quantity" cannot be applied.
Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::CustomerId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 3 to variable "User::CustomerId" cannot be applied.
Warning: 0x80019002 at MasterTest: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (12) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
     Dts.TaskResult = Dts.Results.Success

任何帮助将不胜感激

推荐答案

我的一位同事刚刚给了我答案.

One of my coworkers just give me the answer.

您不需要 ForEach 循环容器或 RecordSet 容器.

You don't need the the ForEach Loop Container or the RecordSet Container.

您所需要的只是平面文件源和 OLE DB 命令.连接到您的数据库并在 OLE DB 命令中选择适当的连接.

All you need is the Flat File Source and an OLE DB Command. Connect to your database and inside the OLE DB Command select the appropriate connection.

在组件属性中输入以下 SQLCommand:

In the Component Properties enter the following SQLCommand:

exec ssis_createorder ?, ?, ? 

?"是参数的占位符.

下一步在列映射选项卡下将 .csv 文件列映射到存储过程参数.

Next under the Column Mappings tab map the .csv file columns to the stored procedure parameters.

您已完成并运行包.

谢谢 Gary，如果你在 StackOverFlow 上，我会给你一个赞并接受你的回答.

Thanks Gary if you were on StackOverFlow I would give you an upvote and accept your answer.

这篇关于SSIS 使用 .CSV 文件 SQL Server 2005 中的参数执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！