gpt4 book ai didi

ssis - 如何使用SSIS包将带有 header 和明细数据的平面文件加载到数据库中?

转载 作者:行者123 更新时间:2023-12-04 18:14:38 28 4
gpt4 key购买 nike

我必须加载一个平面文件,该文件具有不同的标题和具有可变列数的详细信息。这些有亲子关系。如何将数据加载到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

最佳答案

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

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

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

分步过程:


使用“ SQL脚本”部分下提供的脚本在数据库中创建5个名为dbo.AMTdbo.DTMdbo.ISAdbo.QTYdbo.REF的表以及一个名为dbo.UpdateHierarchy的存储过程。表AMTDTMQTYREF将存储类似命名的段的数据,而表ISA将包含所有其他段的数据。
创建一个名为SQLServer的OLE DB Connection以连接到SQL Server实例,并创建一个名为Flat File ConnectionSource,如屏幕快照#“” 1“”-#4中所示。平面文件连接将使用EDI文件。删除Column delimiter,因为文件中元素的数量有所变化。本示例将使用Script Component拆分元素。
在SSIS包上,创建5个变量,如屏幕快照#5所示。另外,将Data Flow taskExecute SQL Task放在程序包的Control Flow选项卡上,如屏幕截图5所示。
使用Flat File SourceScript ComponentMulticastConditional Split和5 OLE DB Destinations配置数据流任务,如屏幕截图6所示。
配置Flat File Source以使用平面文件连接名称Source读取EDI文件。
如屏幕快照#7和#8所示,将Script Component配置为转换任务。使用“脚本组件变量”部分下提供的数据,使用名称和数据类型创建所有其他变量。将Script Component Transformation任务中存在的ScriptMain类替换为“脚本组件代码”下提供的代码。
如屏幕快照9所示配置Conditional Split
配置OLE DB Destination AMT,以将数据插入表dbo.AMT和映射字段中,如屏幕快照#10所示。
配置OLE DB Destination数量以将数据插入表dbo.QTY和映射字段中,如屏幕快照#11所示。
配置OLE DB Destination REF以将数据插入表dbo.REF和映射字段中,如屏幕快照#12所示。
配置OLE DB Destination DTM,以将数据插入表dbo.DTM和映射字段中,如屏幕快照#13所示。
配置OLE DB Destination其他以将数据插入表dbo.ISA和映射字段,如屏幕快照#14所示。
在“数据流”选项卡上,配置Execute SQL Task,如屏幕快照15所示。
屏幕截图#16和#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]


脚本组件代码:

可以在 SSIS 2005 and above中使用的VB.NET代码。

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 - 如何使用SSIS包将带有 header 和明细数据的平面文件加载到数据库中?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6231414/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com