SQL Server Export Unicode & Import via SSIS

Question

(SQL Server 2008)

So here's my task .. I need to export query results to file, and then import that file using SSIS to another DB.

Specific to the task, the data contains every awkward unicode character you can think of, so delimiting with commas, pipes etc is out of the question.

Here are the options SSMS gives me for export format:

• Column Aligned
• Comma/Tab/Space delimited
• Custom delimiter

And here are the options SSIS gives me for a flat file data source:

• Delimited (custom)
• Fixed Width
• Ragged Right

So given that a delimiter character is out of the question ... I cannot see another method that both SSMS & SSIS agree on. Such as fixed width ?

Seems strange that the 2 closely related MS products have such different options. Or have I missed something here ?

Any advice appreciated !!

Answer 1

It seems you need to try out different combination of options while creating delimited flat file(for your exported query result).

Try setting Code page to UTF-8 with and without Unicode. Also use Text qualifier as " or any of your choice which you thought might work. Also try using different option for column delimiter.

Once you are able to create delimited file then you have to apply same setting on file while importing to another DB.