SSIS 数据转换 [英] SSIS Data Transformation

问题描述

我正在尝试将数据从一个数据库传输到一个新数据库.我正在从一张表中获取数据说我获取了人名，然后我将其插入到表人中.这将生成一个我想插入到地址表中的 personID.使用 SSIS 的方法应该是什么.有什么建议吗?

I am trying to transfer data from one db to a new one. i am fetching data from one table say i fetch name of person, then i insert this into say Table Person. this will generate a personID which i want to insert into say Address Table. What should be the approach using SSIS. Any suggestions?

推荐答案

这里有几种方法.

1. 如果这是表的一次性初始加载，我建议在插入语句之前使用 SET IDENTITY_INSERT ON.这将允许您自己插入身份，从而无需检索密钥.您必须使用脚本任务或类似任务来创建键序列.
2. 使用 SQL 命令任务执行插入语句，然后执行 SELECT SCOPE_IDENTITY() 将插入的标识提取到您从 SQL 命令任务返回的参数中.这是有风险的，因为您无法保证 @@identity 来自您的插入.如果其他用户正在进行多次插入，这才是真正的风险.

因此，如果您决定选择 #2，请按照以下方式进行.

So if you decide to go for #2, here is how you would do it.

创建两个表来代表您的旧系统和新系统:

Create Two Tables to represent your Old Sytem and New System:

CREATE TABLE [dbo].[Person](
    [ID] [int] IDENTITY(100,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[Person_OldSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL
) ON [PRIMARY];

用两个条目填充人员表:

Fill The person Table with two entries:

替代文字 http://www.freeimagehosting.net/uploads/ff56e32bea.gif

创建一个存储过程来执行插入并返回新的 id:

Create a stored procedure to do the inserts and return the new id:

ALTER PROCEDURE [dbo].[sp_InsertPerson] 
    @Fname varchar(50),
    @Lname  varchar(50),
    @id integer OUTPUT
AS
BEGIN

    INSERT INTO QandA..Person (FirstName, LastName) VALUES (@Fname, @Lname);
    SELECT @id = SCOPE_IDENTITY();

END

接下来，我们将设置 SSIS 包.在此示例包中，添加单个数据流任务.在数据流任务中添加以下任务并将它们连接起来，如图所示.

Next, we'll set up the SSIS package. In this sample package, add a single Data Flow task. In the data Flow Task add the following tasks and wire them up as shown.

替代文字 http://www.freeimagehosting.net/uploads/5348332a9e.gif

请注意，数据查看器会随着我们的进展向您展示结果.

Note the data viewers are there to show you the results as we progress.

设置 OLE DB 源任务以从 Person_OldSystem 表中提取所有列.

Set up the OLE DB Source Task to pull all of the columns from the Person_OldSystem table.

设置派生列任务以添加名为NewID"的列

Set up the Derived Column Task to add a column called 'NewID'

替代文字 http://www.freeimagehosting.net/uploads/a5c6c9e7c6.gif

使用以下 SQL 设置 OLE DB 命令任务.

Set up the OLE DB Command Task with the following SQL.

EXEC sp_InsertPerson ?, ?, ? OUTPUT

在 OLE DB 命令任务的高级属性中，设置以下列映射:

In the OLE DB Command Task's Advanced Properties, set up the following column mappings:

替代文字 http://www.freeimagehosting.net/uploads/2224622431.gif

所以我们对数据流所做的是从旧系统中提取人员列表.然后，我们添加一个名为 NewID 的新列，以在将行插入新表时保存该行的标识.

So what we've done with the data flow is to extract the list of people from the old system. We then add a new column, called NewID, to hold the identity of the row when it is inserted into the new table.

替代文字 http://www.freeimagehosting.net/uploads/8162127377.gif

OLE DB 命令调用我们的存储过程，该过程在最后一个参数中插入并返回新行的 id.然后将此返回的 id 映射到我们为其准备的数据流中的列中.

The OLE DB Command calls our stored procedure which does the insert and reutrn the id of the new row in the last parameter. This returned id is then mapped into the column in the data flow we preared for it.

替代文字 http://www.freeimagehosting.net/uploads/97dbfba277.gif

条件拆分是为了让数据流去某个地方.

The conditional split is there to give the data flow somewhere to go.

这篇关于SSIS 数据转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！