Filtering out required records from a flat file and merging multiple records into one output record

elinzeyjr 21 Reputation points
2022-10-29T17:31:03.697+00:00

Hope Everyone is doing fine.

I'm still new to SSIS and was hoping someone may have already had a similar requirement and could point me in the right direction.

Requirements: Read in a text file that has multiple record types. Each time a record starting with the characters 'J9' is found, take that record and the 4 records that follow, merge them into one output record to be written out to a new text file. All output records would be written to the same output file (1 file in, and 1 file out).

Input Data Sample:

Header
KC FLDA FLDB FLDC
FLDA FLDB FLDC

J9 FLDA FLDB FLDC
FLDD FLDE FLDF
FLDG FLDH FLDI
FLDJ FLDK FLDL
FLDM FLDN FLDO
KC FLDA FLDB FLDC
FLDA FLDB FLDC
J9 FLDA FLDB FLDC
FLDD FLDE FLDF
FLDG FLDH FLDI
FLDJ FLDK FLDL
FLDM FLDN FLDO

Desired Ouput:

J9 FLDA FLDB FLDC FLDD FLDE FLDF FLDG FLDH FLDI FLDJ FLDK FLDL FLDM FLDN FLDO
J9 FLDA FLDB FLDC FLDD FLDE FLDF FLDG FLDH FLDI FLDJ FLDK FLDL FLDM FLDN FLDO

Any feedback is appreciated.

Thank you

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,459 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,286 Reputation points
    2022-10-31T02:42:07.317+00:00

    Hi @elinzeyjr ,

    I did some local test and online research, it is hard to meet your requirement via SSIS.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments