Software

Ask something
Question Locked

Multiple Rows Combined Into One Row with Flat Files & SSIS

0 helpful
2 replies

by csmith 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have seen several articles and code snipets for combining multiple rows into one row. I would like to share an example and then ask a few questions.

Flat file contains record type, sequence number and detail - ALL AS A ONE COLUMN ROW as input on SSIS. This flat file may contain up to 12 different detail records (at least one seq # 01 but other seq #'s are optional) for a record type and all 12 of those detail records need to be filled in onto one output record (also on a flat file). There will be null values for sequence numbers that did not have an associated input record. AND there can be multiple record types on the file.

Rec Seq# Det.- varies on seq # all same lngth
A20 01 2418571 405 W. 1stSeattleWA93186
A20 02 090909222 40050 AABB2124
A20 03 20202020
A20 04 Left Side impact
A20 07 settlement pending as of 10/20/07
A20 11 100000 12/31/06
A20 01 2522223 3535 South ParkerDetroit
A20 05 3rd circuit court
A20 01 2603030 PO Box 27BaltimoreMD20110
A24 01 2121211Mike Hammonds
A24 02 Married Filing Jointly
A24 06 10030303 404440440 12121111
A24 12 Returned Not Resolved 11222
A24 01 2211188Steven Jessops
A24 01 2222001Mary Wright 2200 222

Output should contain 3 A20 records and 3 A24 records. Output is also comma delimited between output columns
The first A20 record would have detail information for seq# 1 - 4, 7 & 11. Seq#'s 6, 8 - 10 & 12 woulb be null.

Questions -

Can SSIS handle this? I believe it can because you can write Excel macros to move the data (MID function has worked well)

The client would like a SSIS package so is this an efficient way to give them a flat file?

Has anyone done anything similar to this? Is it a simple process?

Any assistance would be greatly appreciated.

2 replies

by vinbrown

I have a very similar case and I am having a lot of difficulty finding examples of how to handle this. It seems that splitting the data out based on the type of record is not hard using a conditional split. The problem lies in linking the related records together. How can you set a variable that spans over multiple rows? If that could be done, that variable would be the link between the records. I would really appreciate any insight you could offer on this matter.

This discussion is closed to new replies.