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
