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 st? p? 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:

BS0220131264202400000130001000000015826727000000000S?ren Hesth

BS0220131264202400000330001000000015826727000000000adfasdf

BS0520131264202410001130001000000015827207000000000 tekst der skal st? p? 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.

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
|||

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.

|||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
|||

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.