July 8, 2010 at 3:11 pm
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
July 9, 2010 at 11:45 pm
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