Home > SSIS Best Practices > Import UTF-8 data into Microsoft SQL Server 2008

Import UTF-8 data into Microsoft SQL Server 2008

Goal – Need to import a UTF-8 file into SQL Server 2008

If you Google “UTF-8 SQL Server”, you will read somewhere on MSDN that SQL Server does not support UTF-8 codepage.

I need to import a UTF-8 encoded file recently into SQL Server 2008.

SQL Server certainly supports Unicode. So I know I can import the UTF-8 file. The question is how.

Solution – Use the SQL Server Import and Export Wizard

The steps are actually very straightforward.

Step 1: Open the SQL Server Import and Export Wizard. Select Flat File Source as the Data Source. It’s important to select 65001 (UTF-8) as the code page. Then click Next.

image 

Step 2: On the next page, since I am not using any column delimiter, I selected {CR} as the column delimiter, and click Refresh to see the sample data. 

image

Step 3: click the Advanced choice on the left. On the advanced window, you need to change the Data Type to Unicode string [DT_WSTR] for every column. The OutputColumnWidth needs to be the maximum for the column, but not exceeding the destination column. Click Next.

image 

Step 4: On the next page, select the destination Database. Click Next.

image

Step 5: On the next page, select the destination table. click Next.

image

Step 6: Verify the mapping. Click Next.

image

Finally click Finish. The Import will start.

image

Categories: SSIS Best Practices
  1. Robert Carnegie
    January 20, 2011 at 2:27 am

    Belated reaction since I just saw this… I expect an early need to import a UTF-8 flat file (with variable number and size of fields in the file) so I am keen to try it out – with SQL Server 2005.

    However, our senior technician has persuaded management to standardise on SQL Server 2005 without SSIS on all servers, on the grounds that it consumes resources. I hope this amuses YOU. I have to use BCP – but not this time!

    Incidentally… SQL Server 2005 apparently does not really have “genuine” Unicode but “merely” UCS-2. That’s 2005, anyway. I don’t expect that this will affect me, since I mostly only need to support the languages of the United Kingdom, but you never know…

    Like

    • Sherry Li 李雨
      January 21, 2011 at 2:32 pm

      Thanks for sharing Robert. It’s indeed amusing that any organization would standardise on SQL Server 2005 without SSIS on all servers. But it’s not surprising. Many people still are afraid of using SSIS. Deployment of packages scare them, compared to writing a stored procedure and simply compiling right on the server. In terms of consuming resources, I am not sure. What do I know is that the processing parallelism capability in Integration Services, and the fact that it allows you to design a more robust architecture for your ETL process make it a must-have for any ETL project.

      Like

  2. JJ
    March 22, 2013 at 11:28 am

    Thank you very much for this information – it saved me a lot of time..

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: