Join two text files in SSIS

Hi,

I'm trying to join outputs of two text files combined in to one text file. The lenght of both the input text files are same (590)

For example,

If contents of text_file_1.txt is: aaaaaaaaaaaaaaaaaaaaaaaaaaaa

and contents of text_file_2.txt is: bbbbbbbbbbbbbbbbbbbbbbbbbbbb

My final text file should be :

final_text.txt

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

I use flat file source for both the input file and then use "Union all" and then write out to final text file destination.

The issues is:

The second file (bbbbbbbbbbbbbbbbbbbbbb) is coming up first in the output and then there is no carriage return.

My output looks like:

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb...(goes up to 590)aaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaa

What is that I'm missing here. Could some one plese help.

Thanks

Sri

This is a really odd request.

I would suggest that you not bother with the Union All step. You can't necessarily predict the order that the results of a union query will come out in. Better to process the first file and "Create" your output file from that, and then process the second file and "Append" the results to the first file.

The bigger question is why would you be doing this with SSIS at all?

Some clarification questions:

1. Does each file only contain one "line" of data (one record?)

2. Does either file have a carriage return at the end of the line already?

3. Is the length of the data fixed (590) or variable?

If you are still stuck, the clarification questions will help the rest of us come up with a better solution.

Dylan.

|||

It sounds like you are using a fixed width destination file. If you want a carriage return at the end of each row, use Ragged Right.

|||Good point, I forgot about that, but it won't fix the order of the result set.|||

Thanks Jwelch. Yes, I was using 'fixed with'. Ragged Right fixed the carriage return problem. Now my output file is properly aligned. But still I'm not able to control the sequence as Dylan72 pointed out.

The union all puts the contents of second file first and first file second.

Answers to Dylan72 question

1. Does each file only contain one "line" of data (one record?)

No. the fist file contains few hundred rows; the second file contains strictly one row (footer record)

2. Does either file have a carriage return at the end of the line already?

I used fixed width, {CR}(LF} for the flatfile destination.

3. Is the length of the data fixed (590) or variable?

The fist file is fixed 590, the second is 42 but I defined the output column width as 590

for the second file, so that both the files merge fine.

I know this is an odd request but that's how the vendor needs it.

Let me quickly summarize what I'm doing. To cut long story short,

In step 1, after a typical ETL process, the records (590 length) is written out to

a destination flat file (fixed width (now changed to ragged right), {CR}(LF}, column out put defined as 590). This step works well. I got the out put that I needed.

In Step2, vendor wants to add a footer record which is in the format

<20 Z's><record count, 11 positions><9's 11position> So a typical footer record

will look like:

ZZZZZZZZZZZZZZZZZZZZ0000000876599999999999

This step also works great, I got the desired output. The destination flat file for step2 is defined as the same like step1 (fixed with (now changed to ragged right), {CR}(LF}, column out put defined as 590)

So I got both the files in a way I want it.

Now I have to join both the text files so that contents of the first file appears first and the

footer record file (second file) is appended to the first file.

I used Union all to do this join, but not able to control the sequencing, always the footer record (second file) shows up first.

I hope I explained it clearly what I'm doing.

I will keep trying different things.

If anyone has a better approach let me know.

Thanks again Dylan72 and Jwelch for the valuable inputs.

-Sri

|||

Two ways I can think of to do this. One Dylan72 has already suggested - use two dataflows, one to process the first set of rows, and the second set to append the final row.

Two, if you really want to do this in one flow, use a Derived Column transform to add a column to both input files in the data flow. Set the column to a value of 1 for the first file, and a value of 2 for the second one. Run both through the Union All, then run the output of that through a Sort transform, using the new column. Then they will be in the proper order to write to the flat file. Just ignore the sort order column when mapping the columns in the flat file destination.

|||

Another approach is to use a script component to join the text files. This gives you the most control over the results. It requires some Visual Basic.net coding.

Here is an article demonstrating how to create a script source component (scroll down to the flat file example): http://technet.microsoft.com/en-us/library/ms136060.aspx