Sql-server – Problems Exporting Long Columns to Flat File Using SSIS

sql serversql-server-2016ssisssis-2016

We have an SSIS package that is going to generate files to be consumed by Google Big Query. The files will be gzipped .tsv files.

One of the requirements is that the file be UTF-8. We have set this in the flat file destination so that it's 65001 – UTF-8. After this, the generated gzip files are consumed to Big Query correctly.

The problem now is that some fields have character lengths of up to 21,000 characters. The DT_WSTR doesn't allow for this size.

Changing the flat file destination field to DT_NTEXT yields the following error message

Error: 0xC020802E at 14_3 Data Flow into MyTSV, Flat File Destination MyDestination [12]: The data type for "Flat File Destination MyDestination.Inputs[Flat File Destination Input].Columns[Value]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

All solutions I have read involve converting back to DT_WSTR or changing the code page back to 1252 which is not an option due to the Big Query code page requirement and the length of the data. Is there another solution for this problem?

Best Answer

Try clicking the Unicode Checkbox

Try the following 65001 code page and Unicode checkbox, it got rid of the error message for me. Hopefully we're on similar versions of SSDT. My Version is 15.

But first, double-click your flat file destination, and click the Update button...

Related Solutions

Sql-server – SQL Server imports data in wrong charset with SSIS

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".

Converting Non Unicode string to Unicode string SSIS

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...

Related Question