如何使用 SSIS 包将带有标题和详细数据的平面文件加载到数据库中? [英] How to load a flat file with header and detail data into a database using SSIS package?

查看：30
本文介绍了如何使用 SSIS 包将带有标题和详细数据的平面文件加载到数据库中?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

推荐答案

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

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.

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

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 列将对文件的数据进行分组，以避免从另一个文件中提取的数据引用错误的父 ID.SetId 对于加载到这些表中的每个文件来说都是唯一的.

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.

分步过程:

  1. 创建 5 个表，名为 dbo.AMT、dbo.DTM、dbo.ISA，dbo.QTY 和 dbo.REF 以及数据库中名为 dbo.UpdateHierarchy 的存储过程，使用 SQL 脚本<下提供的脚本/strong> 部分.表AMT、DTM、QTY 和REF 将存储类似命名的段的数据，表ISA 将包含所有其他段数据.

  1. 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.

创建一个名为 SQLServer 的 OLE DB Connection 以连接到 SQL Server 实例并创建一个名为 的 Flat File Connection>Source 如屏幕截图 #""1"" - #4 所示.平面文件连接将使用 EDI 文件.删除 Column delimiter 因为文件具有不同数量的元素.此示例将使用 Script Component 拆分元素.

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.

在 SSIS 包上，创建 5 个变量，如屏幕截图 #5 所示.此外，将 Data Flow task 和 Execute SQL Task 放在包的 Control Flow 选项卡上，如截图 #5 所示.

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.

使用Flat File Source、Script Component、Multicast、Conditional Split配置数据流任务> 和 5 OLE DB 目标 如屏幕截图 #6 所示.

Configure the Data Flow Task with Flat File Source, Script Component, Multicast, Conditional Split and 5 OLE DB Destinations as shown in screenshot #6.

配置Flat File Source 以使用Flat File 连接名称Source 读取EDI 文件.

Configure the Flat File Source to read the EDI file using the Flat File connection name Source.

将脚本组件配置为转换任务，如屏幕截图#7和#8所示.使用脚本组件变量部分下提供的数据，使用名称和数据类型创建所有其他变量.将 Script Component Transformation 任务中的 ScriptMain 类替换为 Script Component Code 下提供的代码.

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.

配置 Conditional Split，如屏幕截图 #9 所示.

Configure the Conditional Split as shown in screenshot #9.

配置OLE DB Destination AMT 以将数据插入表dbo.AMT 并映射字段，如屏幕截图所示#10.

Configure the OLE DB Destination AMT to insert data into the table dbo.AMT and map fields as shown in screenshot #10.

配置 OLE DB Destination QTY 以将数据插入表 dbo.QTY 并映射字段，如截图所示#11.

Configure the OLE DB Destination QTY to insert data into the table dbo.QTY and map fields as shown in screenshot #11.

配置 OLE DB Destination REF 以将数据插入表 dbo.REF 并映射字段，如屏幕截图所示#12.

Configure the OLE DB Destination REF to insert data into the table dbo.REF and map fields as shown in screenshot #12.

配置OLE DB Destination DTM 以将数据插入表dbo.DTM 并映射字段，如屏幕截图所示#13.

Configure the OLE DB Destination DTM to insert data into the table dbo.DTM and map fields as shown in screenshot #13.

配置 OLE DB Destination Other 以将数据插入表 dbo.ISA 并映射字段，如截图所示#14.

Configure the OLE DB Destination Other to insert data into the table dbo.ISA and map fields as shown in screenshot #14.

在数据流"选项卡上，配置执行 SQL 任务，如屏幕截图 #15 所示.

On the Data Flow tab, configure the Execute SQL Task as shown in screenshot #15.

屏幕截图 #16 和 #17 显示了示例包执行.用于测试此示例的文件数据在 EDI 文件内容 部分下提供.

Screenshots #16 and #17 shows sample package execution. File data used for testing this example is provided under section EDI File Content.

屏幕截图 #18 显示了表 dbo.AMT 中的数据与表 dbo.ISA 中的数据.

Screenshot #18 shows data in table dbo.AMT in comparison with data in table dbo.ISA.

屏幕截图 #19 显示了表 dbo.QTY 中的数据与表 dbo.ISA 中的数据的对比.

Screenshot #19 shows data in table dbo.QTY in comparison with data in table dbo.ISA.

屏幕截图 #20 显示了表 dbo.REF 中的数据与表 dbo.ISA 中的数据.

Screenshot #20 shows data in table dbo.REF in comparison with data in table dbo.ISA.

屏幕截图 #21 显示了表 dbo.DTM 中的数据与表 dbo.ISA 中的数据.

Screenshot #21 shows data in table dbo.DTM in comparison with data in table 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 及更高版本.

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 文件内容:

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:

这篇关于如何使用 SSIS 包将带有标题和详细数据的平面文件加载到数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆