当前位置：文江博客话题详情

ssis edi

如何使用 SSIS 包将带有标题和详细数据的平面文件加载到数据库中？

发布于 2024-11-11 16:41:28 字数 1021 浏览 5 评论 0 原文

我必须加载一个具有不同标题和详细信息以及可变列数的平面文件。这些有亲子关系。如何将数据加载到SQL Server中？该文件如下所示：

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

原文

I have to load a flat file that has different header and detail with variable number of columns. These have parent child relations. How to load the data into SQL Server? The file looks like this:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

楠木可依 2024-11-18 16:41:28

以下是将此文件加载到 SQL Server 中的一种可能方法。下面显示的示例读取 EDI 823 Lockbox 文件的内容并连同关系一起加载到多个表中。我确信还有其他更好的方法可以做到这一点。这只是将 EDI 文件加载到 SQL Server 中的一个示例。

该示例逐行读取 EDI 文件，然后根据字符星号 (*) 拆分它们。脚本组件将值分配给脚本组件中的变量以填充表。在数据流任务填充数据后，执行SQL任务将更新表dbo.AMT中的ParentId列、dbo.DTM、dbo.QTY 和 dbo.REF 使用存储过程 dbo.UpdateHierarchy >。表“ISA”包含所有其他段的数据。屏幕截图显示了数据如何存储在子表中。

所有表中使用的 SetId 列将对文件的数据进行分组，以避免从另一个文件中提取的数据引用错误的父 ID。 SetId 对于加载到这些表中的每个文件来说都是唯一的。

分步过程：

创建 5 个名为 dbo.AMT、dbo.DTM 的表，数据库中的 dbo.ISA、dbo.QTY 和 dbo.REF 以及名为 dbo.UpdateHierarchy 的存储过程使用 SQL 下提供的脚本脚本部分。表AMT、DTM、QTY和REF将存储命名相似的段的数据以及表ISA 将包含所有其他段数据。
创建一个名为 SQLServer 的 OLE DB 连接 以连接到 SQL Server 实例，并创建一个名为 Source< 的平面文件连接 /code> 如屏幕截图所示#""1"" - #4。平面文件连接将使用 EDI 文件。删除列分隔符，因为文件的元素数量不同。此示例将使用脚本组件分割元素。
在 SSIS 包上，创建 5 变量，如屏幕截图 #5 所示。另外，将一个数据流任务和一个执行SQL任务放在包的控制流选项卡上，如屏幕截图#5所示。
使用平面文件源、脚本组件、多播、条件拆分和<配置数据流任务strong>5 OLE DB 目标，如屏幕截图 #6 所示。
配置平面文件源以使用平面文件连接名称Source读取EDI文件。
将脚本组件配置为转换任务，如屏幕截图#7和#8所示。使用脚本组件变量部分下提供的数据使用名称和数据类型创建所有其他变量。将脚本组件转换任务中的ScriptMain类替换为脚本组件代码下提供的代码。
配置条件拆分，如屏幕截图 #9 所示。
配置 OLE DB 目标 AMT 以将数据插入表 dbo.AMT 并映射字段，如屏幕截图所示 # 10.
配置 OLE DB 目标 QTY 以将数据插入表 dbo.QTY 并映射字段，如屏幕截图所示 # 11.
配置 OLE DB 目标 REF 将数据插入表 dbo.REF 并映射字段，如屏幕截图所示 # 12.
配置 OLE DB 目标 DTM 将数据插入表 dbo.DTM 并映射字段，如屏幕截图所示 # 13.
配置 OLE DB 目标 其他，将数据插入表 dbo.ISA 并映射字段，如屏幕截图所示 # 14.
在“数据流”选项卡上，配置执行 SQL 任务，如屏幕截图 #15 所示。
屏幕截图 #16 和 #17 显示示例包执行情况。用于测试此示例的文件数据在 EDI 文件内容 部分下提供。
屏幕截图 #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 及更高版本。

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：

屏幕截图 #< strong>2:

屏幕截图 #3:

屏幕截图 #4：

屏幕截图 #5：

屏幕截图 # 6：

屏幕截图 #7：

屏幕截图 #8：

屏幕截图 # 9：

屏幕截图 #10：

屏幕截图 #11:

屏幕截图#12:

屏幕截图 #13:

屏幕截图 #14：

屏幕截图 #15：

屏幕截图 #16 strong>:

屏幕截图 #17:

屏幕截图 #18：

屏幕截图 #19：

屏幕截图#20:

屏幕截图 #21:

Here is one possible way of loading this file into SQL Server. Below shown example reads the contents of EDI 823 Lockbox file and loads into multiple tables along with the relationship. I am sure that there are other better ways of doing this. This is just one example of loading an EDI file into SQL Server.

The example reads the EDI file line by line and then splits them based on the character asterisk (*). The script component assigns the value to the variables in the Script Component to populate the tables. After the data is populated by the Data Flow Task, the Execute SQL Task will update the ParentId column in the tables dbo.AMT, dbo.DTM, dbo.QTY and dbo.REF using the stored procedure dbo.UpdateHierarchy. The table 'ISA' contains the data of all other segments. Screenshots show how the data is stored in the child tables.

SetId columns used in all the tables will group data of a file in order to avoid wrong parent id being referred from data pulled from another file. SetId will be unique for each file loaded into these tables.

Step-by-step process:

Create 5 tables named dbo.AMT, dbo.DTM, dbo.ISA, dbo.QTY and dbo.REF and a stored procedure named dbo.UpdateHierarchy in the database using the scripts provided under SQL Scripts section. Tables AMT, DTM, QTY and REF will store the data of the segments named similarly and the table ISA will contain all other segment data.
Create an OLE DB Connection named SQLServer to connect to the SQL Server instance and create a Flat File Connection named Source as shown in screenshots #""1"" - #4. Flat File connection will use the EDI file. Remove the Column delimiter because the file has varying number of elements. This example will split the elements using the Script Component.
On the SSIS package, create 5 variables as shown in screenshot #5. Also, place a Data Flow task and an Execute SQL Task on the Control Flow tab of the package as shown in screenshot #5.
Configure the Data Flow Task with Flat File Source, Script Component, Multicast, Conditional Split and 5 OLE DB Destinations as shown in screenshot #6.
Configure the Flat File Source to read the EDI file using the Flat File connection name Source.
Configure the Script Component as Transformation task as shown in screenshots #7 and #8. Create all the other variables using the names and data types using the data provided under Script Component Variables section. Replace the class ScriptMain present within the Script Component Transformation task with the code provided under Script Component Code.
Configure the Conditional Split as shown in screenshot #9.
Configure the OLE DB Destination AMT to insert data into the table dbo.AMT and map fields as shown in screenshot #10.
Configure the OLE DB Destination QTY to insert data into the table dbo.QTY and map fields as shown in screenshot #11.
Configure the OLE DB Destination REF to insert data into the table dbo.REF and map fields as shown in screenshot #12.
Configure the OLE DB Destination DTM to insert data into the table dbo.DTM and map fields as shown in screenshot #13.
Configure the OLE DB Destination Other to insert data into the table dbo.ISA and map fields as shown in screenshot #14.
On the Data Flow tab, configure the Execute SQL Task as shown in screenshot #15.
Screenshots #16 and #17 shows sample package execution. File data used for testing this example is provided under section EDI File Content.
Screenshot #18 shows data in table dbo.AMT in comparison with data in table dbo.ISA.
Screenshot #19 shows data in table dbo.QTY in comparison with data in table dbo.ISA.
Screenshot #20 shows data in table dbo.REF in comparison with data in table dbo.ISA.
Screenshot #21 shows data in table dbo.DTM in comparison with data in table dbo.ISA.

Hope that helps.

SQL Scripts:

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

Script Component Variables:

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]

Script Component Code:

VB.NET code that can be used in 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 File Content:

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

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4: