Serious Converting Data Problem

Hot Topic

sql autoincrement
Testing ODBC connection manager
CLR Enabled
SQL Server 2000 failed to attach database
Restoring database in SQL server 2005 Beta 2
How do i watch variable values during debugging ?
Setting HttpExpires Metabase Property by VBScript
selections
Can I use DSN for OLE DB connection?
LoadFromSQLServer method

SQL Server

External Stylesheet in a database
Management Studio Still will not install
Supporting Multiple Character Sets
URGENT:spsbackup.exe gives logon errors
Second instance of Reporting Service cannot get to web page
Replication error with sqlserver mobile:A request to send data to the computer running IIS is failed, see Hrresult"
Split txt file into multiple
Manipulate Page-Numbers
AdventureWorks DB sample
Moving a SQL2K publisher to a SQL 2005 instance.

Serious Converting Data Problem

Hi,

I am going to map a SQL Server 2005 table to a SQL Server 2000 table.

Here are databases specifications:

SQL Server 2000:
Collation: SQL_Latin1_General_CP1256_CI_AS
Table: Party
Feilds:
Serial int not null
FullName varchar(50)

SQL Server 2005:
Collation: Arabic_CI_AS
Table: CMN_tblParty
Fields:
PartyID int not null
FullName nvarchar(50)

I used SSIS to map data from 2005 to 2000 but it failed. Actually it can't convert unicode field to non unicode one. Therefore I used a Data Conversion Block and I converted the FullName from CMN_tblParty to string with CodePage 1256. But when I connect this block to OLEDB Destination block, a validation error happens which mentions that it can't map data from CodePage 1256 to a 1252 one.

This shows that the SQL Server 2005 assume the SQL_Latin1_General_CP1256_CI_AS as 1252 (I don't know why) and if I change my data conversion block that it converts the FullName from CMN_tblParty to Codepage 1252, it gives an error in runtime which shows that it can't convert nvarchar from Codepage 1256 to a varchar - codepage 1252.

I used also the export data wizard in order it generate the package itself. But this also failed due to conversion problem.

As another solution I checked the DTS on SQL Server 2000 and it could successfully export data to SQL Server 2005 with no conversion problem.

Please let me know how I can run my conversion using SSIS since SSIS enhancements are greate and I am using much of it features in Conversion and Lookups.

Samy

Answer this question

Serious Converting Data Problem

  • Randy Cragin

    I don't know why it is not interpreting the code page correctly.

    What you can do though is set the default code page on the ole db source and also set the "always use default code page".

    Using these two options I was able to transfer data between a column with collation

    SQL_Latin1_General_CP1256_CI_AS and Arabic_CI_AS

  • GoldnGreen

    From the description you're posting it does appear to be a collation issue. Can you change the codepage on the destination column to see if it moves the problem around That won't be the final resolution of course, but it might highlight the issue.

    As an emergency workaround you can use a DTS task in SSIS to do the import for you.

    Buck


  • PFerns

    SSIS is defintely not interpreting the code page of the source table correctly. I am not sure if this is an issue with SSIS or the code page conversion in the OS.

  • Serious Converting Data Problem
Answer this question