This is a question about Microsoft SQL Server Integration Service (SSIS), and if necessary, please help us set it to the correct category. Thank you.

We have a required format of flat file, it’s a text file with multiple columns of fixed length, and each record corresponds to one line in the text file. In this format, the line ends with \x00\x00\x0d\x0a, which means two NUL characters followed by line breaker. See the screenshots below for a demonstration in text and Hex editors.

With SSIS (SQL Server Integration Service), we want to retrieve data from a query, e.g.:

select
*
, convert(varchar(4), ”+char(0)+char(0)+char(13)+char(10)) as end_tag
from source_table
;

Then we want to route the data flow into a Flat File Destination to export to a text file. However, as a result, the end_tag did not leave anything in the result text file.

Is it possible to get our expected line ending tag in SSIS package? If yes, how to do it?

We are new to this area of SSIS, so we highly appreciate any hints and suggestions.

The following screenshots shows our expected line ending:

​This is a question about Microsoft SQL Server Integration Service (SSIS), and if necessary, please help us set it to the correct category. Thank you. We have a required format of flat file, it’s a text file with multiple columns of fixed length, and each record corresponds to one line in the text file. In this format, the line ends with \x00\x00\x0d\x0a, which means two NUL characters followed by line breaker. See the screenshots below for a demonstration in text and Hex editors. With SSIS (SQL Server Integration Service), we want to retrieve data from a query, e.g.: select
*
, convert(varchar(4), ”+char(0)+char(0)+char(13)+char(10)) as end_tag
from source_table
;Then we want to route the data flow into a Flat File Destination to export to a text file. However, as a result, the end_tag did not leave anything in the result text file. Is it possible to get our expected line ending tag in SSIS package? If yes, how to do it?We are new to this area of SSIS, so we highly appreciate any hints and suggestions.  The following screenshots shows our expected line ending:  Read More

​