SSIS Export To UTF-8 or UNIX (or how to set your code page to output a flat file for unix)

As more and more companies are moving to heterogenous environments with both windows and unix systems the life of the Microsoft SQL Server developer is becoming much more interesting. For our recent data warehouse project I was asked to create a SQL Server 2012 Integration Services (SSIS) ETL package to output a text file for consumption by another system. No problem! I can do that in my sleep!  Unfortunately, the target system required my file to be in a UTF-8 format. Um, what? I live in happy little Windows land. No comprende! After a bit of trial and error with my friends on the unix side of the house (“how come the hyphens are showing up as <96>?”) I tracked down the following stackoverflow link which tipped me off to how to get it done: “SSIS – Flat file always ANSI never UTF-8 encoded“. Using the info in the post I ran through the following steps.

1. Open the Data Flow Task

ssis to unix1

2. Right click on your source and select “Show Advanced Editor”.

3. Select Component Properties.

4. Set AlwaysUseDefaultCodePage to “True”.

5. Enter “65001” for the Default Code Page.

6. Click OK.

ssis to unix2

7. Don’t be scared just because your flat file destination now has an ugly red X on it.

ssis to unix3

8. Right click on your Flat File Connection Manager (you may have named it something different than “Flat File Connection Manager”) and click “Edit”.

ssis to unix4

9. Set the Code page to 65001 (UTF-8). Note, the values in the drop down are sorted as if they are text and there are only two rows which start with “6” so it’s easy to miss them. Also, you must have the “unicode” check box empty in order to select a code page.

10. Click OK.

ssis to unix5

11. Oh no! There’s a slightly less scary yellow triangle on the flat file destination now! Take deep breaths.

ssis to unix6

12. Right click on the flat file destination and select “Show Advanced Editor”.

ssis to unix7

13. Click “Refresh” on the Connection Managers tab.

14. Click OK.

ssis to unix8

15. Execute your package and feel happy when the Unix guys praise your skills.

ssis to unix9









One response to “SSIS Export To UTF-8 or UNIX (or how to set your code page to output a flat file for unix)”

  1. Went through all of these steps but it keeps reverting the field back to NText and causes errors.

Leave a Reply

Your email address will not be published.