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.
