SSIS flat file destination, how to write line ending \\x00\\x00\\x0d\\x0a?

Question

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:

• 
• 

Answer 1

Hello,

Thank you for posting in Microsoft Support Community.

Based on the description, I understand your question is related to SQL Server. Since SQL Server is not currently supported in the Microsoft Support Community, it is recommended to repost your question in Microsoft Q&A, where dedicated engineers will provide you with professional and effective answers.

https://learn.microsoft.com/en-us/answers/questions/

Click the "Ask a Question" button in the upper right corner to post your question and select the "SQL Server" tag.

Thank you for your understanding and support. If you have any question or concern, please feel free to let us know.