Flat file - row delimiter problem

Hi,

I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information.

I use a SQL script like this (ex.):

SELECT '

BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+

'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '

BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal sta pa kortet' as nvarchar

FROM dbo.WAIT_Applicant WA (nolock)

This makes the text (from one record) split up over several lines in the output.

I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore

In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:

BS0220131264202400000130001000000015826727000000000Soren Hesth

BS0220131264202400000330001000000015826727000000000adfasdf

BS0520131264202410001130001000000015827207000000000 tekst der skal sta pa kortet

But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.

I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.

Answer this question

Flat file - row delimiter problem

Arik1234
If what you want is something like the following (Assume #is a comment line and doesn't exist in the file).

#Record1
Column1Column2
Column3Column4
Column5Column6
#Record2
Column1Column2
Column3Column4
Column5Column6
...

If that is what you want, then you will need to build both a custom component either through a script transform or a full-fledged component.

The code would be something similar to the following

Dim sw As New System.IO.StreamWriter("c:\temp\test.txt", True)

sw.WriteLine(Row.Column1.ToString & Row.Column2.ToString)
sw.WriteLine(Row.Column3.ToString & Row.Column4.ToString)
sw.WriteLine(Row.Column5.ToString & Row.Column6.ToString)

sw.Close()

This will append to an existing file, so if you may need to create a task that deletes the existing file prior to the data flow task. You'll also should check for errors (null values, stream writer was created, etc).

Larry Pope
Ben805
I'm guessing you want the Flat File to output records on individual lines (if you opened the file in notepad). If that is the case use {cr}{lf} as your row delimiter, since in a Windows environment that is the standard newline character combination. As single {lf} is often employed in Unix/Mainframe environments as a newline character, which is why it is an option.

Larry Pope
Danny Furnis

But i succeded with doing this with my the package i wrote in SQL server 2000.

There must be a way that I can make a carriage return, so that the notepad will read it correctly.
MintyEggNog
That was also what i started with, but then i read in another discussion inhere, where they suggested to use {LF}, so i changed it.

What i want is, that one record is printed over several lines in the text-file. After that record, then the next record is printed, also over several lines in the text-file.

I have tried to change it back and tried almost every possible combination of

- rowdelimiter ( CRLF, CR, LF...)

- format (ragged right, delimied...)

and so on.

And nothing works.

Is there any other way of doing this. Maybe there is something I can do in the script.
Flat file - row delimiter problem

Answer this question