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.
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.
7. Don't be scared just because your flat file destination now has an ugly red X on it.
8. Right click on your Flat File Connection Manager (you may have named it something different than "Flat File Connection Manager") and click "Edit".
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.
11. Oh no! There's a slightly less scary yellow triangle on the flat file destination now! Take deep breaths.
12. Right click on the flat file destination and select "Show Advanced Editor".
13. Click "Refresh" on the Connection Managers tab.
14. Click OK.
15. Execute your package and feel happy when the Unix guys praise your skills.