我有一个简单的SSIS包,我想让它复杂一点.
现在,它在OLE DB源中执行存储过程,并将从存储过程返回的行添加到数据流中.然后,对于返回的每一行,它执行OLE DB命令转换,执行第二个存储过程(在第二个数据库中),从源传递列作为参数.
第二个存储过程执行同步功能,我想记录添加,删除和更新的总数. “sync”存储过程使用MERGE语句的OUTPUT子句获取此数据并将其作为结果集返回.
解决方法
这并不像它应该的那样直截了当.那或我需要回到SSIS课程.
OLE DB命令组件无法向数据流添加新行,因为它是同步组件.
它也无法向数据流添加新列.这是第一件不直观的事情.因此,您将在我的源代码中看到,我添加了一个类型为nvarchar(10)的ActionName列/字符串长度为10.如果您愿意,可以在OLE DB命令组件之前的派生列转换中添加该列.
由于我无法向数据流添加行,这意味着我只能使用OUTPUT参数进行处理,而不是使用它可以生成的记录集.也许你的存储过程只允许一次改变一行,这是可以的,但对我来说有一般的代码味道.
表定义和设置
CREATE TABLE dbo.so_27932430
(
SourceId int NOT NULL,SourceValue varchar(20) NOT NULL
);
GO
INSERT INTO
dbo.so_27932430
(SourceId,SourceValue)
VALUES
(1,'No change'),(3,'Changed');
存储过程
CREATE PROCEDURE
dbo.merge_27932430
(
@SourceId int,@SourceValue varchar(20),@ActionName nvarchar(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@BloodyHack table
(
ActionName nvarchar(10) NOT NULL,SourceId int NOT NULL
);
MERGE
dbo.so_27932430 AS T
USING
(
SELECT
D.SourceId,D.SourceValue
FROM
(
SELECT @SourceId,@SourceValue
) D(SourceId,SourceValue)
) AS S
ON
(
T.SourceId = S.SourceId
)
WHEN
MATCHED
AND T.SourceValue <> S.SourceValue
THEN
UPDATE
SET
T.SourceValue = S.SourceValue
WHEN
NOT MATCHED THEN
INSERT
(
SourceId,SourceValue
)
VALUES
(
SourceId,SourceValue
)
OUTPUT
$action,S.SourceId
INTO
@BloodyHack;
/* Pick one,any one*/
SELECT
@ActionName = BH.ActionName
FROM
@BloodyHack AS BH
END
来源查询
SELECT
D.SourceId,D.SourceValue,CAST(NULL AS nvarchar(10)) AS ActionName
FROM
(
VALUES
(1,(2,'I am new'),'I Changed')
) D(SourceId,SourceValue);
OLE DB命令设置
EXECUTE dbo.merge_27932430 @SourceId = ?,@SourceValue = ?,@ActionName = ? OUTPUT;
结果
参考
BIML
假设您有免费的BidsHelper,则使用以下Biml生成此包.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=sqlNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="so_27932430">
<Variables>
<Variable DataType="String" Name="QuerySource">
<![CDATA[SELECT
D.SourceId,SourceValue);
]]></Variable>
<Variable DataType="String" Name="QueryCommand">EXECUTE dbo.merge_27932430 @SourceId = ?,@ActionName = ? OUTPUT;</Variable>
</Variables>
<Tasks>
<Dataflow Name="DFT OLEDB Test">
<Transformations>
<OleDbSource ConnectionName="CM_OLE" Name="OLESRC GenData">
<VariableInput VariableName="User.QuerySource" />
</OleDbSource>
<OleDbCommand ConnectionName="CM_OLE" Name="OLECMD Test">
<DirectInput>EXECUTE dbo.merge_27932430 @SourceId = ?,@ActionName = ? OUTPUT;</DirectInput>
<Parameters>
<Parameter SourceColumn="SourceId" DataType="Int32" TargetColumn="@SourceId"></Parameter>
<Parameter SourceColumn="SourceValue" DataType="AnsiString" Length="20" TargetColumn="@SourceValue"></Parameter>
<Parameter SourceColumn="ActionName" DataType="String" Length="10" TargetColumn="@ActionName"></Parameter>
</Parameters>
</OleDbCommand>
<DerivedColumns Name="DER PlaceHolder" />
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
