How to concatenate 2 files in SSIS (Integration Services)?

Question

I have 2 files that do not have the same columns. I want to be able to concatenate them, but I can't seem to find a way to do this. I thought about using a Script Component, but that still wants you to declare output columns.

The first file has columns and data like this:

A|B|C|D|E|F|G
Adata1|Bdata1|Cdata1|Ddata1|Edata1|Fdata1|Gdata1
Adata2|Bdata2|Cdata2|Ddata2|Edata2|Fdata2|Gdata2
Adata3|Bdata3|Cdata3|Ddata3|Edata3|Fdata3|Gdata3
Adata4|Bdata4|Cdata4|Ddata4|Edata4|Fdata4|Gdata4

The second file has columns and data like this:

H|I|J|K|L|M|N
Hdata1|Idata1|Jdata1|Kdata1|Ldata1|Mdata1|Ndata1
Hdata2|Idata2|Jdata2|Kdata2|Ldata2|Mdata2|Ndata2
Hdata3|Idata3|Jdata3|Kdata3|Ldata3|Mdata3|Ndata3
Hdata4|Idata4|Jdata4|Kdata4|Ldata4|Mdata4|Ndata4

I need the overall output file to look like this:

A|B|C|D|E|F|G
Adata1|Bdata1|Cdata1|Ddata1|Edata1|Fdata1|Gdata1
Adata2|Bdata2|Cdata2|Ddata2|Edata2|Fdata2|Gdata2
Adata3|Bdata3|Cdata3|Ddata3|Edata3|Fdata3|Gdata3
Adata4|Bdata4|Cdata4|Ddata4|Edata4|Fdata4|Gdata4
H|I|J|K|L|M|N
Hdata1|Idata1|Jdata1|Kdata1|Ldata1|Mdata1|Ndata1
Hdata2|Idata2|Jdata2|Kdata2|Ldata2|Mdata2|Ndata2
Hdata3|Idata3|Jdata3|Kdata3|Ldata3|Mdata3|Ndata3
Hdata4|Idata4|Jdata4|Kdata4|Ldata4|Mdata4|Ndata4

I know it's out of the ordinary, but that's the requirements...

Any suggestions?

You don't need to declare output columns if all you are doing is writing a file. Do you need to keep the data in SSIS after you've written it? — saarrrr
No need to keep the data in SSIS after it's been written. Do I just have a free-standing Script Component? It looks like I can't have multiple inputs. — Mark
no need to use ssis here. Just the command line: copy /b file1 + file2 outputfile — N West
Yeah, the command line would work, but this is part of a larger process that I do need ssis for — Mark

saarrrr saarrrr · Accepted Answer · 2015-09-03T20:01:29

Go with a script task. If you have connection managers for your 3 files, you don't need any inputs to the task.

Access the connection strings with:

var fileAString = Dts.Connections["fileAConnectionManagerName"].ConnectionString;

Workflow can be, open fileC for write. Open fileA for read. Read data and write to fileC. Close fileA. Open fileB for read. Read data and write to fileC. Close files and return success.

How to concatenate 2 files in SSIS (Integration Services)?

1 Answers