SSIS使用Parent Child将多个平面文件放入表中

This is my tables in SQL Server:

这是我在SQL Server中的表：

Table: Customer (CustNum is the PK and auto increments)

CustNum  Firstname  Lastname
123      Bob        Smith
456      John       Paton
789      Fred       Bloggs

Table: Job  (CustNum is the FK, JobNum is the PK and auto increments)

JobNum  CustNum   Item     Desc
852      123      ABCDE    Widgets
654      456      WERT     More Widgets
987      789      QWE      Mouse mat

I then have data coming from two csv's in the below format

然后我得到来自以下格式的两个csv的数据

Table: Customer 

Firstname  Lastname
Bob        Smith
John       Paton
Fred       Bloggs

Table: Job

Item     Desc
ABCDE    Widgets
WERT     More Widgets
QWE      Mouse mat

I need to import the customers then the jobs into SQL tables but maintain referential integrity. When the Job record is created, it needs to lookup for the correct CustNum - how do I do this? I have found many examples doing this from one CS file, but not from multiple csv's. Also, there is no reliable key to tie the job rows to the customer table - I don't think I will be able to use the SSIS lookup - will I?

我需要将客户然后将作业导入SQL表，但保持参照完整性。创建作业记录后，需要查找正确的CustNum - 我该怎么做？我从一个CS文件中找到了很多这样的例子，但是没有从多个csv中做到这一点。此外，没有可靠的密钥将作业行绑定到客户表 - 我不认为我将能够使用SSIS查找 - 我会吗？

2 个解决方案

#1

You can use an SSIS Package to feed your data base.

您可以使用SSIS包来提供数据库。

the package will look like it

包装看起来像它

Result Customer

结果客户

CustNum     Firstname  Lastname
----------- ---------- ----------
1           Bob        Smith
2           Fred       Bloggs
3           John       Paton

Result

结果

JobNum      CustNum     Item       Desc
----------- ----------- ---------- --------------------------------------------------
1           1           ABCDE      Widgets
2           2           QWE        Mouse mat
3           3           WERT       More Widgets

How to do it:

怎么做：

Main task:

主要任务：

Add a DTS to Customer

向客户添加DTS
Add a DTS to Job

将DTS添加到作业
Create the flow (green arrow) from Customer to Job

创建从客户到作业的流程（绿色箭头）

Customer DTS

客户DTS

Add a connection to Customer File

添加与客户文件的连接
Add a connection to Customer SQL

添加与Customer SQL的连接
Sort Both outputs by Firstname, Lastname

按名字，姓氏对两个输出进行排序
Configure the merge join task like the image

像图像一样配置合并连接任务
Insert and configure the conditional Split with one output called "new" and expression ISNULL(CustNum)

插入并配置条件Split，其中一个输出名为“new”，表达式为ISNULL（CustNum）
Insert to the Customer table

插入Customer表

Job DTS

工作DTS

Insert a connection to Job file

插入与作业文件的连接
Insert a connection to Customer table

插入与Customer表的连接
Sort both outputs by Firstname, Lastname

按名字，姓氏对两个输出进行排序
Configure the join like the image:

像图像一样配置连接：
Insert a connection to Job table

插入与Job表的连接
Sort both by CustNum, Item

按CustNum，Item排序
Insert and configure the conditional Split with one output called "new" and expression ISNULL(JobNum)

插入并配置条件Split，其中一个输出名为“new”，表达式为ISNULL（JobNum）
Insert to the Job table

插入作业表

#2

I was able to change the source data so it appears in 1 CSV file as opposed to being in 2. I was then able to use the SQL Lookup function in SSIS and it now works. Thanks for all those who posted.

我能够更改源数据，使其显示在1个CSV文件中而不是2中。然后，我可以在SSIS中使用SQL查找功能，现在可以使用了。感谢所有发布的人。

#1