As you work with large scale SQL Server Integration Services (SSIS) ETL processes and sequences, you may come across the need to handle UTF-8 encoded text files. Without proper handling, UTF-8 / Unicode characters can cause issues with your SSIS load tasks. In this article, we will explore some ideas for handling UTF-8 file formats with SSIS.

Understanding UTF-8 and Unicode

Before we dive into the solutions, let’s provide some background about UTF-8 and Unicode. Unicode is an encoding standard maintained by the Unicode Consortium, which includes major players in the technology field such as Google, SAP, Microsoft, and Oracle. The purpose of Unicode is to standardize the numbering scheme assigned to represent a letter or character across all languages, countries, software products, and hardware platforms.

UTF-8, on the other hand, is an encoding method for handling all the characters in the Unicode set. It stands for Unicode Transformation Format and acts as a decryption key to map backend bytes to the actual characters displayed on the screen. It is widely used and popular for web pages.

Working with SSIS and UTF-8 Unicode Data

Now that we have a basic understanding of UTF-8 and Unicode, let’s put this knowledge into practice with SSIS. We’ll start by creating a UTF-8 encoded text file with some special characters. You can use a tool like NotePad++ to ensure the correct encoding format.

When working with a bar delimited file, you can easily load the data with minimal adjustment to the flat file connection. You’ll need to create a connection to the flat file in SQL Server Data Tools (SSDT) or SSDT (formerly known as BIDS). Make sure to set the code page to 65001 (UTF-8) and adjust the data types for string fields to Unicode string [DT_WSTR].

For fixed length files, the process is a bit more complex. You may encounter issues with column widths and special characters pushing other characters to the right. One workaround is to load the entire row of data into a single column in a staging table, using the NVARCHAR data type. Then, you can use SQL Server’s SUBSTRING function to parse the data into the appropriate column parts.

Conclusion

Handling UTF-8 encoded text files in SSIS can be challenging, especially when dealing with special characters and fixed length files. While SSIS supports various code page encoding formats, it may not load data from UTF-8 encoded files as expected. However, by following the solutions outlined in this article, you can successfully handle UTF-8 file formats in SSIS.

Remember to adjust the code page, data types, and use appropriate parsing techniques when working with UTF-8 encoded files. Although this issue has been acknowledged by Microsoft, it has not been addressed in SQL Server 2012, so we may need to continue using workarounds for the near future.