Solving an SSIS Error – Cannot convert between Unicode and non-Unicode

  • There is nothing wrong with nvarchar/nchar fields.

    Unless you look at all the double operations that must occur, and the extra disk space required to store as nvarchar/nchar.

    The size of my staging and Data warehouse are enough that conversion saves gigabytes of disk space, reduces foreign key relationship times, not to mention indexes....etc...

  • Thanks for the article! It is good info to see the capability of SSIS. It is ok not to have specific info. I understand the point now is that if you have a source field that is NVARCHAR, it is possible to bring the data to a VARCHAR destination.

  • There is nothing wrong with nvarchar/nchar fields. Unless you look at all the double operations that must occur, and the extra disk space required to store as nvarchar/nchar.

    This is why I would advocate that each column needs to be evaluated on it's own. In practice, I find most columns can be set to varchar (and probably should have been so in the source data). But some fields, particularly those that deal with names of any kind and addresses need to remain nvarchar to properly preserve the data.

  • It would be interesting to know how performance varies between the two options (CAST in source and data conversion in package).

    For those with separate servers running SSIS, doing the data conversion in the package might be a useful way of keeping the load down on the main SQL instance.

    Otherwise I agree with the majority: performing the CAST in T-SQL is the more straightforward solution & helps keep SSIS packages clean and streamlined.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • From my experience, not much if any.

    The way that the SSIS engine does these conversion, it has to create a separate stream.

    You simply move the complexity from SQL Server (well documented and tunable) to the SSIS application server that is well frankly not as much of a known entity.

    It also depends on the load ie what other packages is the application server trying to process concurrently.

  • Nice article Thomas, thanks.

  • As per the other comments it easier to maintain the SQL code than SSIS derivation task and probably better to push the load onto the SQL server.

    I know this example was done for training, in a Production environment I would do the same except put the SQL select statement into a stored proc, this makes production changes easier, only the stored proc needs to change, no need to re-deploy the SSIS package(project). The stored proc should also run more optimally than the select script in SSIS.

    The environment I'm working has all the sources coming in as text or spreadsheets and then need to convert the type in a data conversion or derived column transformer. Is there a faster way to get around this having to manually convert the types to deal with unicode/non-unicode conversion?

  • Is there a faster way to get around this having to manually convert the types to deal with unicode/non-unicode conversion?

    Not that I know of.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sean Peffer (12/19/2016)


    As per the other comments it easier to maintain the SQL code than SSIS derivation task and probably better to push the load onto the SQL server.

    I know this example was done for training, in a Production environment I would do the same except put the SQL select statement into a stored proc, this makes production changes easier, only the stored proc needs to change, no need to re-deploy the SSIS package(project). The stored proc should also run more optimally than the select script in SSIS.

    The environment I'm working has all the sources coming in as text or spreadsheets and then need to convert the type in a data conversion or derived column transformer. Is there a faster way to get around this having to manually convert the types to deal with unicode/non-unicode conversion?

    Most times I import text files into a staging table first with data types discovered by the Source component, then use T-SQL to convert the staging tables to destination tables

    Thomas LeBlanc, MVP Data Platform Consultant

  • Thomas LeBlanc (12/19/2016)


    Sean Peffer (12/19/2016)


    As per the other comments it easier to maintain the SQL code than SSIS derivation task and probably better to push the load onto the SQL server.

    I know this example was done for training, in a Production environment I would do the same except put the SQL select statement into a stored proc, this makes production changes easier, only the stored proc needs to change, no need to re-deploy the SSIS package(project). The stored proc should also run more optimally than the select script in SSIS.

    The environment I'm working has all the sources coming in as text or spreadsheets and then need to convert the type in a data conversion or derived column transformer. Is there a faster way to get around this having to manually convert the types to deal with unicode/non-unicode conversion?

    Most times I import text files into a staging table first with data types discovered by the Source component, then use T-SQL to convert the staging tables to destination tables

    Text files' data types can be controlled directly in SSIS, so this is less of a problem than it is within Excel.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply