以下是将此文件加载到 SQL Server 中的一种可能方法。下面显示的示例读取内容EDI 823 密码箱文件并与关系一起加载到多个表中。我确信还有其他更好的方法可以做到这一点。这只是将 EDI 文件加载到 SQL Server 中的一个示例。
该示例逐行读取EDI文件，然后根据字符进行分割asterisk(*)。脚本组件将值分配给脚本组件中的变量以填充表。数据填充后Data Flow Task, the Execute SQL Task将更新ParentId表中的列dbo.AMT, dbo.DTM, dbo.QTY and dbo.REF使用存储过程dbo.UpdateHierarchy。表“ISA”包含所有其他段的数据。屏幕截图显示了数据如何存储在子表中。
SetId所有表中使用的列将对文件的数据进行分组，以避免从另一个文件中提取的数据引用错误的父 ID。SetId对于加载到这些表中的每个文件来说都是唯一的。
分步过程：
Create 5名为的表dbo.AMT, dbo.DTM, dbo.ISA, dbo.QTY and dbo.REF和一个名为的存储过程dbo.UpdateHierarchy使用下面提供的脚本在数据库中SQL脚本部分。表格AMT, DTM, QTY and REF将存储类似命名的段和表的数据ISA将包含所有其他段数据。
创建一个OLE DB Connection named SQL服务器连接到 SQL Server 实例并创建Flat File Connection named Source如截图所示 #""1"" - #4。平面文件连接将使用 EDI 文件。去除Column delimiter因为该文件具有不同数量的元素。此示例将使用以下方式拆分元素Script Component.
在 SSIS 包上，创建5变量如屏幕截图所示#5。另外，放置一个Data Flow task and an Execute SQL Task on the Control Flow包的选项卡如屏幕截图所示#5.
配置数据流任务Flat File Source, Script Component, Multicast, Conditional Split and 5 OLE DB Destinations如截图所示#6.
配置Flat File Source使用平面文件连接名称读取 EDI 文件Source.
配置Script Component as 转型任务如屏幕截图所示#7 and #8。使用下面提供的数据使用名称和数据类型创建所有其他变量脚本组件变量部分。更换班级脚本主程序存在于Script Component Transformation使用下面提供的代码进行任务脚本组件代码.
配置Conditional Split如截图所示#9.
配置OLE DB Destination AMT将数据插入表中dbo.AMT和映射字段，如屏幕截图所示#10.
配置OLE DB Destination QTY将数据插入表中dbo.QTY和映射字段，如屏幕截图所示#11.
配置OLE DB Destination REF将数据插入表中dbo.REF和映射字段，如屏幕截图所示#12.
配置OLE DB Destination DTM将数据插入表中dbo.DTM和映射字段，如屏幕截图所示#13.
配置OLE DB Destination Other将数据插入表中dbo.ISA和映射字段，如屏幕截图所示#14.
在“数据流”选项卡上，配置Execute SQL Task如截图所示#15.
截图#16 and #17显示示例包执行情况。用于测试此示例的文件数据在部分下提供EDI File Content.
截屏 ＃18显示表中的数据dbo.AMT与表中数据比较dbo.ISA.
截屏 ＃19显示表中的数据dbo.QTY与表中数据比较dbo.ISA.
截屏 ＃20显示表中的数据dbo.REF与表中数据比较dbo.ISA.
截屏 ＃21显示表中的数据dbo.DTM与表中数据比较dbo.ISA.
希望有帮助。
SQL 脚本：
CREATE TABLE [dbo].[AMT](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[LineNumber] [int] NULL,
[AmountQualifierCode] [varchar](3) NULL,
[MonetaryAmount] [numeric](18, 2) NULL,
[SetId] [uniqueidentifier] NULL,
CONSTRAINT [PK_AMT] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DTM](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[LineNumber] [int] NULL,
[DateTimeQualifier] [varchar](3) NULL,
[Date] [varchar](10) NULL,
[Time] [varchar](10) NULL,
[TimeCode] [varchar](2) NULL,
[PeriodFormatQualifier] [varchar](3) NULL,
[DateTimePeriod] [varchar](35) NULL,
[SetId] [uniqueidentifier] NULL,
CONSTRAINT [PK_DTM] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ISA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineNumber] [int] NULL,
[SegmentCode] [varchar](3) NULL,
[ReferenceId] [varchar](30) NULL,
[Date] [varchar](10) NULL,
[Time] [varchar](10) NULL,
[IdNumberQualifier] [varchar](2) NULL,
[IdentificationNumber] [varchar](12) NULL,
[AccountNumberQualifier] [varchar](3) NULL,
[AccountNumber] [varchar](35) NULL,
[TransactionHandlingCode] [varchar](2) NULL,
[CreditDebitFlag] [varchar](1) NULL,
[PaymentMethod] [varchar](3) NULL,
[ReferenceIdQualifier] [varchar](3) NULL,
[MonetaryAmount1] [numeric](18, 2) NULL,
[MonetaryAmount2] [numeric](18, 2) NULL,
[AuthorizeInfoQualifier] [varchar](2) NULL,
[AuthorizeInfo] [varchar](10) NULL,
[SecurityInfoQualifier] [varchar](2) NULL,
[SecurityInfo] [varchar](10) NULL,
[InterchangeSenderIdQualifier] [varchar](2) NULL,
[InterchangeSenderId] [varchar](15) NULL,
[InterchangeReceiverIdQualifier] [varchar](2) NULL,
[InterchangeReceiverId] [varchar](15) NULL,
[InterchangeStandardsId] [varchar](1) NULL,
[InterchangeVersionId] [varchar](5) NULL,
[InterchangeControlNumber] [varchar](9) NULL,
[Acknowledge] [varchar](1) NULL,
[TestIndicator] [varchar](1) NULL,
[SubElementSeparator] [varchar](1) NULL,
[FunctionalId] [varchar](2) NULL,
[ApplicationSenderCode] [varchar](12) NULL,
[ApplicationReceiverCode] [varchar](12) NULL,
[Responsbility] [varchar](2) NULL,
[Version] [varchar](12) NULL,
[TransactionIdCode] [varchar](3) NULL,
[TransactionSetControlNumber] [varchar](9) NULL,
[Counter] [int] NULL,
[SetId] [uniqueidentifier] NULL,
CONSTRAINT [PK_ISA] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[QTY](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[LineNumber] [int] NULL,
[QuantityQualifier] [varchar](2) NULL,
[Quantity] [numeric](15, 0) NULL,
[SetId] [uniqueidentifier] NULL,
CONSTRAINT [PK_QTY] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[REF](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[LineNumber] [int] NULL,
[ReferenceIdQualifier] [varchar](3) NULL,
[ReferenceId] [varchar](30) NULL,
[SetId] [uniqueidentifier] NULL,
CONSTRAINT [PK_REF] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[UpdateHierarchy]
AS
BEGIN
SET NOCOUNT ON;
UPDATE AMT
SET AMT.ParentId = COALESCE(PAR.ParentId, 0)
FROM dbo.AMT AMT
CROSS APPLY (
SELECT MAX(Id) AS ParentId
FROM dbo.ISA PAR
WHERE PAR.SetId = AMT.SetId
AND PAR.LineNumber < AMT.LineNumber
AND PAR.SegmentCode IN ('DEP', 'BAT')
) PAR;
UPDATE QTY
SET QTY.ParentId = COALESCE(PAR.ParentId, 0)
FROM dbo.QTY QTY
CROSS APPLY (
SELECT MAX(Id) AS ParentId
FROM dbo.ISA PAR
WHERE PAR.SetId = QTY.SetId
AND PAR.LineNumber < QTY.LineNumber
AND PAR.SegmentCode IN ('DEP', 'BAT')
) PAR;
UPDATE REF
SET REF.ParentId = COALESCE(PAR.ParentId, 0)
FROM dbo.REF REF
CROSS APPLY (
SELECT MAX(Id) AS ParentId
FROM dbo.ISA PAR
WHERE PAR.SetId = REF.SetId
AND PAR.LineNumber < REF.LineNumber
AND PAR.SegmentCode IN ('BPR', 'RMR')
) PAR;
UPDATE DTM
SET DTM.ParentId = COALESCE(PAR.ParentId, 0)
FROM dbo.DTM DTM
CROSS APPLY (
SELECT MAX(Id) AS ParentId
FROM dbo.ISA PAR
WHERE PAR.SetId = DTM.SetId
AND PAR.LineNumber < DTM.LineNumber
AND PAR.SegmentCode IN ('BPR', 'RMR')
) PAR;
END
GO
脚本组件变量：
S.no. Variable name Data Type Length/Precision
1. LineNumber four-byte signed integer [DT_I4]
2. ParentId four-byte signed integer [DT_I4]
3. SegmentCode string [DT_STR] 3
4. ReferenceId string [DT_STR] 30
5. Date string [DT_STR] 10
6. Time string [DT_STR] 10
7. IdNumberQualifier string [DT_STR] 2
8. IdentificationNumber string [DT_STR] 12
9. AccountNumberQualifier string [DT_STR] 3
10. AccountNumber string [DT_STR] 35
11. AmountQualifierCode string [DT_STR] 3
12. TransactionHandlingCode string [DT_STR] 2
13. CreditDebitFlag string [DT_STR] 1
14. PaymentMethod string [DT_STR] 3
15. DateTimeQualifier string [DT_STR] 3
16. TimeCode string [DT_STR] 2
17. PeriodFormatQualifier string [DT_STR] 3
18. DateTimePeriod string [DT_STR] 35
19. QuantityQualifier string [DT_STR] 2
20. Quantity numeric [DT_NUMERIC] 15, 0
21. ReferenceIdQualifier string [DT_STR] 3
22. MonetaryAmount1 numeric [DT_NUMERIC] 18,2
23. MonetaryAmount2 numeric [DT_NUMERIC] 18,2
24. AuthorizeInfoQualifier string [DT_STR] 2
25. AuthorizeInfo string [DT_STR] 10
26. SecurityInfoQualifier string [DT_STR] 2
27. SecurityInfo string [DT_STR] 2
28. InterchangeSenderIdQualifier string [DT_STR] 2
29. InterchangeSenderId string [DT_STR] 15
30. InterchangeReceiverIdQualifier string [DT_STR] 2
31. InterchangeReceiverId string [DT_STR] 15
32. InterchangeStandardsId string [DT_STR] 1
33. InterchangeVersionId string [DT_STR] 5
34. InterchangeControlNumber string [DT_STR] 9
35. Acknowledge string [DT_STR] 1
36. TestIndicator string [DT_STR] 1
37. SubElementSeparator string [DT_STR] 1
38. FunctionalId string [DT_STR] 2
39. ApplicationSenderCode string [DT_STR] 12
40. ApplicationReceiverCode string [DT_STR] 12
41. Responsbility string [DT_STR] 2
42. Version string [DT_STR] 12
43. TransactionIdCode string [DT_STR] 3
44. TransactionSetControlNumber string [DT_STR] 9
45. Counter four-byte signed integer [DT_I4]
46. SetId unique identifier [DT_GUID]
脚本组件代码：
VB.NET可以使用的代码SSIS 2005 and above.
Public Class ScriptMain
Inherits UserComponent
Public Enum Segments
ISA
GS
ST
DEP
AMT
QTY
BAT
BPR
REF
RMR
DTM
SE
GE
IEA
End Enum
Dim arrLine As String()
Dim segmentCode As String
Dim counter As Integer
Dim lineNumber As Integer = 1
Dim segmentSequence As Hashtable = New Hashtable()
Dim setId As Guid = Guid.NewGuid()
Public Overrides Sub PreExecute()
MyBase.PreExecute()
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
arrLine = Row.LineText.Split("*"c)
segmentCode = SegmentValue(arrLine, 0)
Row.SegmentCode = segmentCode
Row.LineNumber = lineNumber
Row.SetId = setId
counter = arrLine.Length
lineNumber += 1
Select Case (segmentCode.ToUpper())
Case Segments.ISA.ToString.ToUpper()
Row.AuthorizeInfoQualifier = SegmentValue(arrLine, 1)
Row.AuthorizeInfo = SegmentValue(arrLine, 2)
Row.SecurityInfoQualifier = SegmentValue(arrLine, 3)
Row.SecurityInfo = SegmentValue(arrLine, 4)
Row.InterchangeSenderIdQualifier = SegmentValue(arrLine, 5)
Row.InterchangeSenderId = SegmentValue(arrLine, 6)
Row.InterchangeReceiverIdQualifier = SegmentValue(arrLine, 7)
Row.InterchangeReceiverId = SegmentValue(arrLine, 8)
Row.Date = SegmentValue(arrLine, 9)
Row.Time = SegmentValue(arrLine, 10)
Row.InterchangeStandardsId = SegmentValue(arrLine, 11)
Row.InterchangeVersionId = SegmentValue(arrLine, 12)
Row.InterchangeControlNumber = SegmentValue(arrLine, 13)
Row.Acknowledge = SegmentValue(arrLine, 14)
Row.TestIndicator = SegmentValue(arrLine, 15)
Row.SubElementSeparator = SegmentValue(arrLine, 16)
Case Segments.GS.ToString.ToUpper()
Row.FunctionalId = SegmentValue(arrLine, 1)
Row.ApplicationSenderCode = SegmentValue(arrLine, 2)
Row.ApplicationReceiverCode = SegmentValue(arrLine, 3)
Row.Date = SegmentValue(arrLine, 4)
Row.Time = SegmentValue(arrLine, 5)
Row.InterchangeControlNumber = SegmentValue(arrLine, 6)
Row.Responsbility = SegmentValue(arrLine, 7)
Row.Version = SegmentValue(arrLine, 8)
Case Segments.ST.ToString.ToUpper()
Row.TransactionIdCode = SegmentValue(arrLine, 1)
Row.TransactionSetControlNumber = SegmentValue(arrLine, 2)
Case Segments.DEP.ToString.ToUpper()
Row.ReferenceId = SegmentValue(arrLine, 1)
Row.Date = SegmentValue(arrLine, 2)
Row.IdNumberQualifier = SegmentValue(arrLine, 5)
Row.IdentificationNumber = SegmentValue(arrLine, 6)
Row.AccountNumberQualifier = SegmentValue(arrLine, 7)
Row.AccountNumber = SegmentValue(arrLine, 8)
Case Segments.AMT.ToString.ToUpper()
Row.AmountQualifierCode = SegmentValue(arrLine, 1)
Row.MonetaryAmount1 = SegmentValue(arrLine, 2)
Case Segments.QTY.ToString.ToUpper()
Row.QuantityQualifier = SegmentValue(arrLine, 1)
Row.Quantity = Convert.ToDecimal(SegmentValue(arrLine, 2))
Case Segments.BAT.ToString.ToUpper()
Row.Date = SegmentValue(arrLine, 1)
Row.ReferenceId = SegmentValue(arrLine, 3)
Case Segments.BPR.ToString.ToUpper()
Row.TransactionHandlingCode = SegmentValue(arrLine, 1)
Row.MonetaryAmount1 = SegmentValue(arrLine, 2)
Row.CreditDebitFlag = SegmentValue(arrLine, 3)
Row.PaymentMethod = SegmentValue(arrLine, 4)
Row.IdNumberQualifier = SegmentValue(arrLine, 6)
Row.IdentificationNumber = SegmentValue(arrLine, 7)
Row.AccountNumber = SegmentValue(arrLine, 9)
Case Segments.REF.ToString.ToUpper()
Row.ReferenceIdQualifier = SegmentValue(arrLine, 1)
Row.ReferenceId = SegmentValue(arrLine, 2)
Case Segments.RMR.ToString.ToUpper()
Row.ReferenceIdQualifier = SegmentValue(arrLine, 1)
Row.ReferenceId = SegmentValue(arrLine, 2)
Row.MonetaryAmount1 = SegmentValue(arrLine, 4)
Row.MonetaryAmount2 = SegmentValue(arrLine, 5)
Case Segments.DTM.ToString.ToUpper()
Row.DateTimeQualifier = SegmentValue(arrLine, 1)
Row.Date = SegmentValue(arrLine, 2)
Row.Time = SegmentValue(arrLine, 3)
Row.TimeCode = SegmentValue(arrLine, 4)
Row.PeriodFormatQualifier = SegmentValue(arrLine, 5)
Row.DateTimePeriod = SegmentValue(arrLine, 6)
Case Segments.SE.ToString.ToUpper()
Row.Counter = Convert.ToInt32(SegmentValue(arrLine, 1))
Row.TransactionSetControlNumber = SegmentValue(arrLine, 2)
Case Segments.GE.ToString.ToUpper()
Row.Counter = Convert.ToInt32(SegmentValue(arrLine, 1))
Row.TransactionSetControlNumber = SegmentValue(arrLine, 2)
Case Segments.IEA.ToString.ToUpper()
Row.Counter = Convert.ToInt32(SegmentValue(arrLine, 1))
Row.TransactionSetControlNumber = SegmentValue(arrLine, 2)
End Select
End Sub
Public Function SegmentValue(ByRef LineArray As String(), ByVal Counter As Integer) As String
If LineArray.Length > Counter Then
Return LineArray(Counter).ToString().Trim()
End If
Return String.Empty
End Function
End Class
EDI 文件内容：
ISA*00* *00* *12*2562379521 *08*YOUR ID *19981223*1056*U*00401*000000017*0*T*>
GS*PD*2562379521*YOUR ID*19981223*1056*000000017*X*004010VICS
ST*852*000000001
DEP*0116960*20110511***01*061000104*DA*1000022220940
AMT*3*13006.05
QTY*41*3
QTY*42*5
BAT*20110511**STAWRRY11051101
AMT*2*9332.33
QTY*42*2
BPR*I*4799*C*CHK*PBC*01*011500010*DA*394001464351
REF*CK*0000001002
BPR*I*4533.33*C*CHK*PBC*01*011500010*DA*394001464351
REF*CK*0000001001
BAT*20110511**STAWRRY11051102
AMT*2*1986.99
QTY*42*2
BPR*I*853.97*C*CHK*PBC*01*111000614*DA*708340062
REF*CK*0010736416
RMR*ST*00090183**853.97*12199.61
REF*BE*01*0123181825*0
REF*SL*NA*191219012318
DTM*810*20110430
BPR*I*1133.02*C*CHK*PBC*01*111000614*DA*708340062
REF*CK*0010736417
RMR*ST*00090184**1133.02*16186.04
REF*BE*01*0123181825*0
REF*SL*NA*191219012318
DTM*810*20110430
BAT*20110511**STAWRRY11051103
AMT*2*1686.73
QTY*42*1
BPR*I*1686.73*C*CHK*PBC*01*075911742*DA*0100461755
REF*CK*0000002795
RMR*ST*00094075**1686.73*42168.16
REF*BE*01*0123181825*0
REF*SL*NA*191219012318
DTM*810*20110331
SE*39*000000088
GE*1*88
IEA*1*000000088
截屏 ＃1:
截屏 ＃2:
截屏 ＃3:
截屏 ＃4:
截屏 ＃5:
截屏 ＃6:
截屏 ＃7:
截屏 ＃8:
截屏 ＃9:
截屏 ＃10:
截屏 ＃11:
截屏 ＃12:
截屏 ＃13:
截屏 ＃14:
截屏 ＃15:
截屏 ＃16:
截屏 ＃17:
截屏 ＃18:
截屏 ＃19:
截屏 ＃20:
截屏 ＃21:
