Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: SSIS: unicode and non-unicode string data types

    Hi guys and gals,

    I've had some great headaches with SSIS this morning, which I have managed to get a workarounds for, but I'm not happy with them so I've come to ask for advice.

    Basically, I am exporting data from an SQL Server database into an Excel spreadsheet and hitting issues with unicode and non-unicode data types.

    For example, I have a column that is char(6) and have added a data conversion step to the data flow, which converts it to type DT_WSTR and then everything works!

    However, this seems like a completely un-neccessary step as I should be able to do the conversion in T-SQL - but no matter what I try I keep getting the same problem.
    Code:
    SELECT Cast(employee_number As nvarchar(255)) As [employee_number]
    FROM   employee
    WHERE  forename = 'george'
    
    Error	Validation error. details: 1 [1123]: Column "employee_number" cannot convert between unicode and non-unicode string data types.
    I know I have a solution (read: workaround) but I really don't want to do this everytime!

    Any suggestions for what else to try?
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post some DDL to recreate the error, dudewan?

    Code:
    USE tempdb
    GO 
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.employee')) BEGIN
        DROP TABLE dbo.employee
    END
    
    CREATE TABLE dbo.employee
        (
            employee_number        CHAR(6)        NOT NULL
            , forename            VARCHAR(11)    NULL
            , CONSTRAINT pk_employee    PRIMARY KEY CLUSTERED (employee_number) WITH (FILLFACTOR = 100)
        )
    GO    
    
    INSERT INTO dbo.employee (employee_number, forename)
    SELECT    'gVee', 'George'
    
    SELECT CAST(employee_number As NVARCHAR(255)) As [employee_number]
    FROM   employee
    WHERE  forename = 'george'
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.employee')) BEGIN
        DROP TABLE dbo.employee
    END
    OUTPUT:
    Code:
    employee_number
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    gVee

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That DLL looks just right; the issue is that SSIS still seems to think that the casted value is non-unicode!
    Attached Thumbnails Attached Thumbnails unicode.JPG  
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe, actually, the only thing wrong with the DLL is that employee_number is not the primary key
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Does this error only if you pump it through SSIS or does the query fail in SSMS? Also, I've just been playing with SSIS (as you know it is not really something I have much use for) but it barfed because you haven't used two part naming. You know omitting the schema\ owner is potentially inefficient right?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Hehe, actually, the only thing wrong with the DLL is that employee_number is not the primary key
    I don't even want to know what the story is behind that....

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Query is absolutely fine in SSMS, it's just SSIS that has the beef.
    Quote Originally Posted by pootle flump
    You know omitting the schema\ owner is potentially inefficient right?
    Yep, aware of that - normally do; I actually stripped the schema off the query I posted for security reasons!



    The reason it's not the PK is because a persons employee number can change
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    I don't even want to know what the story is behind that....
    Quote Originally Posted by georgev
    The reason it's not the PK is because a persons employee number can change


    Code:
    USE sandbox
    GO 
    
    IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.georgey_peorgey')) BEGIN
        DROP VIEW dbo.georgey_peorgey
    END
    GO
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.employee')) BEGIN
        DROP TABLE dbo.employee
    END
    
    CREATE TABLE dbo.employee
        (
            employee_number        CHAR(6)        NOT NULL
            , forename            VARCHAR(11)    NULL
            , CONSTRAINT pk_employee    PRIMARY KEY CLUSTERED (employee_number) WITH (FILLFACTOR = 100)
        )
    GO    
    
    INSERT INTO dbo.employee (employee_number, forename)
    SELECT    'gVee', 'George'
    GO
    
    CREATE VIEW dbo.georgey_peorgey
    --WITH SCHEMABINDING
    AS
    
        SELECT CAST(employee_number As NVARCHAR(255)) As [employee_number]
        FROM   employee
        WHERE  forename = 'george'
    
    GO
    I exported via SSIS from that view just fine.....

  9. #9
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Are you allowed to alter the Excel column to string [DT_STR] from Unicode string [DT_WSTR]?

    Right click on the Excel Destination -> Show Advanced Editor -> Input and Output Properties -> Excel Destination Input -> External Columns -> Column -> DataType

    Click image for larger version. 

Name:	ssis1.jpg 
Views:	940 
Size:	95.5 KB 
ID:	8416

    When I did that, I was able to pump a column char(1) from a SQL Server table into Excel without using the data conversion step.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •