如何获得存储过程标量输出并将其添加到管道中的每一行？
如何获得存储过程标量输出并将其添加到管道中的每一行？
提问于 2013-02-20 18:52:03
我正在开发一个SSIS包，以便将数据从ERP系统()传输到定制的CRM解决方案。我需要转移/更新的事情之一是客户。在客户关系管理系统中，有一个称为CustomerID的领域，它是客户关系管理环境特有的，必须在数据从ERP系统传输到CRM系统时设置。
问题是，我必须调用一个存储过程来获取要插入的下一个客户的CustomerID。我怎样才能在SSIS内部做到这一点？我目前正在使用数据流来传输数据，我可以看到的唯一调用存储过程的地方是ADO任务中的stored查询，每次调用该过程都会增加ID，因此每次运行SSIS包时，它都会更新客户关系管理数据库中的所有客户的CustomerID字段。
存储过程驻留在自定义CRM应用程序上。存储过程不接受任何输入参数。它只是在每次调用时输出标量值，如CT000001、CT000002等。
我的其他选择是如何正确地做到这一点呢？
回答 1
Stack Overflow用户
发布于 2013-02-20 20:41:02
您需要使用脚本组件为每一行调用存储过程，并将值添加到下游到目标的管道。
示例SSIS 2012包说明了一个示例：
该示例使用以下数据库对象。
- 名为PracticeDB的Server 2012数据库。在这种情况下，CRM和ERP表都在同一个数据库中，以保持简单。
- 表
dbo.CRM将包含目标客户关系管理数据。
- 表
dbo.ERP保存源ERP数据。
- 表
dbo.CustomerId是客户关系管理系统中的一个假设表，用于增加客户数量。
- 存储过程
dbo.GetCustomerId在调用客户号时会增加它的数量。
创建上述数据库对象的脚本：
下面的脚本将创建上述对象，并将一些行插入源ERP表中。
USE PracticeDB;
GO
CREATE TABLE dbo.CRM(
CRMId int IDENTITY(1,1) NOT NULL,
CustomerId int NOT NULL,
CustomerName varchar(50) NULL
);
GO
CREATE TABLE dbo.ERP(
ERPId int IDENTITY(1,1) NOT NULL,
CustomerName varchar(50) NOT NULL
);
GO
CREATE TABLE dbo.CustomerId(
Id int NOT NULL
);
GO
CREATE PROCEDURE dbo.GetCustomerId
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT Id FROM dbo.CustomerId)
INSERT INTO dbo.CustomerId (Id) VALUES(1);
ELSE
INSERT INTO dbo.CustomerId (Id)
SELECT MAX(Id) + 1 FROM dbo.CustomerId;
SELECT MAX(Id) AS CustomerId FROM dbo.CustomerId;
END;
GO
INSERT INTO dbo.ERP (CustomerName) VALUES
('John Doe')
, ('Jane Doe')
, ('Stephen Smith')
, ('Dean Jones')
;
GO- 创建一个SSIS包并创建一个ADO.NET连接管理器来连接到适当的数据源。
- 将数据流任务拖放到“控制流”选项卡上
- 在数据流任务中，拖放组件
ADO.NET Source、Script Component (Transformation)和ADO.NET目标。
- ADO.NET源将从表dbo.ERP读取数据
- ADO.NET目标将将数据插入到表dbo.CRM中
脚本组件将涉及以下配置。
- 双击脚本组件以打开脚本组件编辑器.
- 单击
Connection Managers选项卡页
- 选择ADO.NET连接管理器
ADONET_PracticeDB，并给它一个类似于ADONET_PracticeDB的名称此连接管理器将在脚本组件代码中使用。
- 单击
Inputs and Outputs选项卡页
- 将输入重命名为
ERP，将输出重命名为CRM
- 展开CRM，选择Output列并单击
Add Column。
- 将列名重命名为CustomerID。该示例使用类型为整型的列。您需要根据需要更改数据类型。
- 单击
Script选项卡页。
- 在“脚本”选项卡页，单击“
Edit Script...”。
- 键入以下脚本组件部分中所示的代码。脚本使用包上可用的ADO.NET连接管理器启动连接，然后在
PreExecute方法中启动命令对象。
- 然后，在
ERP_ProcessInputRow方法中为管道中的每一行调用存储过程，以生成新的客户ids。
C中的脚本组件代码
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb;
using System.Data.SqlClient;
#endregion
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IDTSConnectionManager100 manager;
SqlConnection connection = null;
SqlCommand command = null;
public override void PreExecute()
{
manager = base.Connections.PracticeDB;
connection = new SqlConnection();
connection = (SqlConnection)manager.AcquireConnection(null);
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.GetCustomerId";
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void ERP_ProcessInputRow(ERPBuffer Row)
{
Row.CustomerID = Convert.ToInt32(command.ExecuteScalar());
}
}
- 关闭脚本组件
- 在执行包之前，对数据执行下面的查询以检查行。
表选择查询：
SELECT * FROM dbo.ERP;
SELECT * FROM dbo.CRM;
SELECT * FROM dbo.CustomerId;
在第一次执行包之后，您可以注意到已经插入了4行，并且成功地调用了存储过程来添加客户Ids。
在第二次执行包之后，您可以注意到又插入了4行，并且存储过程成功地增加了客户Ids。
希望这能给你个主意。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14987703
复制相关文章
相似问题
腾讯云开发者
