Using ASCII 31 as field separator and ASCII 30 as record separator in SSIS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Using ASCII 31 as field separator and ASCII 30 as record separator in SSIS

Has anyone used the ASCII 31 and ASCII 30 as field and row separator to create a text file in SSIS? I have over 50 text files I need to export data out from our SQL 2005 database. By using these separators, it would avoid any issue with common separators (ie LF CR, |, etc) existed in the content of a field.

Will those exported files usedby your application or another SQL isntance?

These files are needed by our customers. Some of them use SQL server. I know one is using Oracle. And I am not sure others.

If the source file format doesn’t change then you can use the field seperator, as it is being an user-requirement you can follow by testinga samples.

Thanks for the reply. I am not sure I follow your thought. The source data is from SQL tables. I am trying to use SSIS to turn them into text files with the said field and row delimiter. I tried to use the ‘Flat File Manager’ to create the flat file destination but the delimiter fields don’t allow me to put in custom delimiter. I tried to use the expression there but I don’t think they have the fucntion there for me to specifiy delimiters. I attempted char(30) but the expression doesn’t recognize this char function.
If u can help specify the ASCII 31 and ASCII 30 delimiters in the Flat File Manager, it would be highly appreciated.

See this blog for information.


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |