SSIS merge 2 files with different columns

Question

SSIS merge 2 files with different columns

spin

Hall of Fame

Points: 3274
More actions
September 30, 2019 at 2:03 pm

#3684577
Hi
I am trying to create a pipe delimited file with double quoted qualifier. It also needs to include a header and footer row.
The data comes from SQL and outputs like so....
```
"1"|"Hello"|"a column"|"a person"|"a thing"
"2"|"Goodbye"|"another column"|"another person"|"another thing"
```
The header should read : HEADER|YYYYMMDD
The footer should read : FOOTER|YYYYMMDD|2 <-- this is a row count
..so the final file should be...
```
"HEADER|YYYYMMDD"
"1"|"Hello"|"a column"|"a person"|"a thing"
"2"|"Goodbye"|"another column"|"another person"|"another thing"
"FOOTER|YYYYMMD|2"
```
I have tried using union all but it creates blank pipe delimited value for all the unmapped columns in the header/footer data, eg...
```
"HEADER|YYYYMMDD"|""|""|""|""
"1"|"Hello"|"a column"|"a person"|"a thing"
"2"|"Goodbye"|"another column"|"another person"|"another thing"
"FOOTER|YYYYMMD|2"|""|""|""|""
```
Can anyone help me merge the data?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

One option would be to write them out as 4 separate files then merge them or write them out with different targets to the same file and the first set to overwrite with the other 3 set to append. Another option would be to use a single column as the target and do the formatting of the lines manually.

This reply was modified 5 years, 1 month ago by ZZartin.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

ZZartin wrote:

One option would be to write them out as 4 separate files then merge them or write them out with different targets to the same file and the first set to overwrite with the other 3 set to append. Another option would be to use a single column as the target and do the formatting of the lines manually.

+1, these are your options (though only three separate files are required, and 'merge' is not the best description - they need to be concatenated (copy/append))

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

spin Hall of Fame Points: 3274 More actions · Answer 3

Hi

Thanks for the replies, I ended up with a derived column which concatenated all fields into a single column (with my qualifiers), and union all'd with my header and footer.

🙂

ashley.moller1 Grasshopper Points: 11 More actions · Answer 4

Hi - we have to do this for one of our extract files - simply use 3 Flat File Connections with the same output file as their destination. Create the 3 Data Flow tasks for each of the levels in your output file so that will output the data in the format you need.

Then un-tick "Overwrite data in the file" in the 2nd and 3rd Flat File Destinations. This will append the data to the file rather than overwrighting it. This is an example of the output we have to produce (Fixed width columns for the data):-

0001010RXC DBS 0800552019093000000533015984

101 1944010820010522. DUMMY DUMMY

990101RXC DBS 0800552019093000000533015984

This reply was modified 5 years, 1 month ago by ashley.moller1.