Converting Non-Unicode fields to Unicode on the fly in OLE DB Source -- Solution?

  • We're moving one of our systems running on SQL 2000 to SQL 2008, and I now have the pleasure of converting some 15 DTS packages to SSIS. Many of these packages export data from MS SQL into other databases (mostly MS Access), and I'm running into the whole Unicode vs Non-Unicode conversion problems where SSIS can't save Non-Unicode strings in the Source to a Unicode column in the Destination.

    After creating about 20 some odd Data Conversion tasks to convert the Non-Unicode Strings to Unicode to make SSIS happy I decide to just write a Stored Procedure to convert the columns automatically then call the procedure as a SQL Command from the OLE DB Source in the Data Flow section. This seems to work, and thus far I've rewritten a few DTS packages into SSIS without having to worry about the non-Unicode columns.

    Below is the procedure I wrote, but I'd like some peer review to see if you guys notice anything I'm missing or anything that could lead to problems down the road. The biggest blahness is converting varchar columns with max length > 4000 to nvarchar, but I'm just taking the first 4000 characters and truncating the rest. This probably isn't ideal for some, but in the scenario I'm working with it's fine since the few varchar columns we have with a max length of 4000+ have less than 4000 characters of data.

    Thanks for any feedback or possibly better ideas on how to simplify what I'm setting out to do.

    Take care --

    Sam

    Alter PROCEDURE [dbo].[uspTableSelect_Unicode]

    @strTableNameNVARCHAR(100)

    AS

    BEGIN

    /* Testing

    DECLARE @strTableName NVARCHAR(100)

    SET @strTableName = 'amLoanDetails'

    */

    SET NOCOUNT ON

    SET FMTONLY OFF

    DECLARE @strSQL NVARCHAR(MAX)

    DECLARE @strLength NVARCHAR(4)

    DECLARE @intLoop INTEGER

    DECLARE @intLoopMax Integer

    SET @strSQL = 'Select '

    SET @intLoop = 1

    select @intLoopMax = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @strTableName

    WHILE @intLoop <= @intLoopMax

    BEGIN

    SELECT@strSQL = @strSQL +

    Case

    WHEN Data_Type = 'varchar' AND Character_Maximum_Length <= 4000 THEN

    'cast(' + Column_Name + ' as nvarchar(' + CAST(Character_Maximum_Length AS NVARCHAR(4)) + ')) as ' + Column_Name + ','

    WHEN Data_Type = 'varchar' AND Character_Maximum_Length > 4000 THEN

    'cast(' + left(Column_Name,4000) + ' as nvarchar(4000)) as ' + Column_Name + ','

    WHEN Data_Type = 'char' THEN

    'cast(' + Column_Name + ' as nvarchar(' + CAST(Character_Maximum_Length AS NVARCHAR(4)) + ')) as ' + Column_Name + ','

    WHEN Data_Type = 'text' THEN

    'cast(' + left(Column_Name,8000) + ' as ntext) as ' + Column_Name + ','

    ELSE Column_Name + ',' END

    FROMINFORMATION_SCHEMA.COLUMNS

    WHERETABLE_NAME = @strTableName AND

    Ordinal_Position = @intLoop

    SET @intLoop = @intLoop + 1

    END

    SET @strSQL = LEFT(@strSQL,LEN(@strSQL)-1)

    SET @strSQL = @strSQL + ' From ' + @strTableName

    EXEC(@strSQL)

    END

  • Not sure, will this code is useful to you, however you can avoid looping using below code...

    declare @strSQL varchar(1000)

    set @strSQL =''

    select @strSQL = @strSQL+COALESCE( Case

    WHEN Data_Type = 'varchar' AND Character_Maximum_Length <= 4000 THEN

    ' cast(' + Column_Name + ' as nvarchar(' + CAST(Character_Maximum_Length AS NVARCHAR(4)) + ')) as ' + Column_Name + ','

    WHEN Data_Type = 'varchar' AND Character_Maximum_Length > 4000 THEN

    ' cast(' + left(Column_Name,4000) + ' as nvarchar(4000)) as ' + Column_Name + ','

    WHEN Data_Type = 'char' THEN

    ' cast(' + Column_Name + ' as nvarchar(' + CAST(Character_Maximum_Length AS NVARCHAR(4)) + ')) as ' + Column_Name + ','

    WHEN Data_Type = 'text' THEN

    ' cast(' + left(Column_Name,8000) + ' as ntext) as ' + Column_Name + ','

    ELSE Column_Name + ',' END, '')

    from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'mytable'

    order by ORDINAL_POSITION asc

    select 'SELECT '+ LEFT(@strSQL,LEN(@strSQL)-1) + ' From mytable'

Viewing 2 posts - 1 through 1 (of 1 total)

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