如何解决SSIS包中OLE DB目标中的非空约束？ [英] How to tackle Not Null constraints in OLE DB Destination in SSIS Package?

问题描述

我是SSIS软件包的新手。我想将数据从源转移到具有不同表结构的目标数据库。我的数据流从 OLE DB源到 OLE DB目标

我已将 OLE DB Source 命名为 Patient Source ，并且在OLE DB Source Editor中，该连接是通过源数据库建立的，并且在数据访问模式中，我选择了 SQL命令和SQL命令文本，如下所示

从pat_Patients中选择ppFName，ppLName，ppPersonalNbr，ppEmailOne ，并且我预览了数据，它显示了 75行。 p>

我想将这四个字段从该数据库转移到名为 Person 的源数据库表中，其表结构如上。

连接是在OLE DB目标编辑器中完成的，

运行项目时，数据流如下，

，我遇到了三个错误，其中第一个错误状态

[OLE DB目标[2]]错误：SSIS错误代码DTS_E_OLEDBERROR。发生OLE DB错误。错误代码：0x80004005。
OLE DB记录可用。源： Microsoft SQL Server本机客户端11.0结果：0x80004005说明：该语句已终止。
OLE DB记录可用。源： Microsoft SQL Server本机客户端11.0结果：0x80004005说明：无法将值NULL插入表'test_9.dbo.Person'的列'TenantId'中；该列不允许为空。INSERT失败。。

我知道TenantId和PersonId都不为空，并且外键& 主键身份。

但是我想输入 TenantId 作为' 1 '和 PersonId 作为身份即自动为患者来源中的所有 75行递增。我还想输入 AddedBy 作为 1 和 AddedDateTime 作为系统当前日期时间。

我不知道如何达到上述要求，请任何人帮助我。

在此先感谢。

解决方案

您可以使用派生变换在 TenantId 为空

  ISNULL（[TenantId]）吗？ 1：[TenantId] 
  

Similary for AddedBy 列将表达式添加为 1 ，对于 AddedDateTime 列将表达式添加为 GETDATE（）

I'm new to SSIS Package. I want to transfer data from source to destination database which has different table structures. My Data Flow is from OLE DB Source to OLE DB Destination

I have named OLE DB Source as Patient Source and in OLE DB Source Editor, the connection is made with Source Database and in Data Access Mode I have selected SQL command and SQL command text as follows

select ppFName, ppLName, ppPersonalNbr, ppEmailOne from pat_Patients and as well as I have previewed the data, it shows 75 rows.

I want to transfer these four fields from this database to my source database table named Person and its table structure as above.

The connection is made in OLE DB Destination Editor,

and the mappings as follows,

When I run the project, my data flow is as follows,

and I got three errors, in which first error states

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'TenantId', table 'test_9.dbo.Person'; column does not allow nulls. INSERT fails.".

I know that TenantId and PersonId are both not null and 'foreign key' & 'primary key identity' respectively.

But I want to enter TenantId as '1' and PersonId as Identity i.e., auto-increment for all 75 rows from Patient Source. As well as I want to enter AddedBy as '1' and AddedDateTime to system current datetime.

I don't know how to achieve the above requirements, please anyone help me.

Thanks in advance.

解决方案

You can use derived transformation to assign 1 when the TenantId is null

ISNULL([TenantId]) ? 1 : [TenantId] 

Similary for AddedBy column add an expression as 1 and for AddedDateTime column as GETDATE()

这篇关于如何解决SSIS包中OLE DB目标中的非空约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！