Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

I have change data type in advanced editor but now i have this error
the output column "ACCOUNT_CODE" on the error output has properties that do not match the properties of its corresponding data source column.
The data appears as string and i can not change the error output data type.

how can i resolve this ?

Thank you.|||Any luck here? I'm having the same problem. Unicode coming in, bound for a text file so I change the output column data type to string. Trouble is, the error output column data type remains Unicode text stream, and I can't change it.|||have you tried using the data conversion transformation component?|||

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

We have had a number of requests for implicit conversions and we're certainly looking at those. However, it is important to bear in mind that the more SSIS does implicitly, the fewer opportunities you have to capture and respond to errors at a fine grain of detail. Of course, there will be some business cases where such fine-grained error handling is less important than others - Unicode Ansi conversion may well be one of them.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

I have tried a standard OLEDB input/output using the MS Oracle driver, and also with a data conversion component specifying the input and output columns as DT_WSTR. The data-flow runs with no errors, yet the data loaded into SQL is incorrect.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.

Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

I have change data type in advanced editor but now i have this error
the output column "ACCOUNT_CODE" on the error output has properties that do not match the properties of its corresponding data source column.
The data appears as string and i can not change the error output data type.

how can i resolve this ?

Thank you.|||Any luck here? I'm having the same problem. Unicode coming in, bound for a text file so I change the output column data type to string. Trouble is, the error output column data type remains Unicode text stream, and I can't change it.|||have you tried using the data conversion transformation component?|||

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

We have had a number of requests for implicit conversions and we're certainly looking at those. However, it is important to bear in mind that the more SSIS does implicitly, the fewer opportunities you have to capture and respond to errors at a fine grain of detail. Of course, there will be some business cases where such fine-grained error handling is less important than others - Unicode Ansi conversion may well be one of them.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

I have tried a standard OLEDB input/output using the MS Oracle driver, and also with a data conversion component specifying the input and output columns as DT_WSTR. The data-flow runs with no errors, yet the data loaded into SQL is incorrect.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.

Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

I have change data type in advanced editor but now i have this error
the output column "ACCOUNT_CODE" on the error output has properties that do not match the properties of its corresponding data source column.
The data appears as string and i can not change the error output data type.

how can i resolve this ?

Thank you.|||Any luck here? I'm having the same problem. Unicode coming in, bound for a text file so I change the output column data type to string. Trouble is, the error output column data type remains Unicode text stream, and I can't change it.|||have you tried using the data conversion transformation component?|||

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

We have had a number of requests for implicit conversions and we're certainly looking at those. However, it is important to bear in mind that the more SSIS does implicitly, the fewer opportunities you have to capture and respond to errors at a fine grain of detail. Of course, there will be some business cases where such fine-grained error handling is less important than others - Unicode Ansi conversion may well be one of them.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

I have tried a standard OLEDB input/output using the MS Oracle driver, and also with a data conversion component specifying the input and output columns as DT_WSTR. The data-flow runs with no errors, yet the data loaded into SQL is incorrect.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.
sql

Importing data

Hello everybody,
I need some help with importing data from txt to sql.
I have done that with BULK INSERT, but i had some limitations:
-i cant supress any field in source or destination.
Anyone can help me?
Thanks
Leandro Loureiro dos SantosLeandro,
> I need some help with importing data from txt to sql.
> I have done that with BULK INSERT, but i had some limitations:
> -i cant supress any field in source or destination.
You can do this with a bcp format using
bulk insert ...
with (formatfile = 'format_file_path')
Linda

importing and exporting tables

Hello Everybody,
Not sure if this is the correct Forum, anyway lemme me know your views. I have SQL Server running in two remote machines (in different geographical locations). I need to import a database from one SQL Server installation to another installation. What is best possible solution:
1) Transfering the .mdf and .ldf file from one installation to another installation
2) Generating the records of all the tables in the database to a common file format CSV and then loading it in destination database. If this is ok then how to generate import and export data using CSV format
3) Any other options?
Srikanth

If you need all data and structures, then the best method is a backup. MDF and LDF will do much the same, it covers everything too, but those files also include free space within the DB, so normally are much larger than required just for data transfer, and for running a system that is correct, just not so nice for copying files around.

|||

also when you back up or move your database files ( I would suggest a backup, it is cleaner) do a database and log shrink to reduce the size of the backup.

Performing a zip file afterwards on the bak will reduce the size more for travel.

|||

I think shrinking database and logs is a very bad idea. It can have serious impact on a production system, such as block transactions if you go too small, and consuming lots of IO. It can also cause fragmentation in the files themselves, and on disk. Good management of files is essential for performance and availability, and shinking does not normally fit with that. Saying that for a small system it may not matter, but it should not be seen as a easy or cheap option.

Zipping will help a lot, you may even want to consider some of third-party tools that compress backups, and genarlly run faster as well compared to native backup. Quest has LiteSpeed, Idera has something and Red Gate have SQL Backup.

|||Thanks a lot for your suggestions. How is CSV formating of the tables in the database. Is it possible? If so how?
|||

You can export data from a table to a CSV, but I think we said backups would be better for all tables etc.

The simplest way to export a table to CSV would be to use the SSIS Import/Export Wizard. There are several ways to invoke this, but try right-clicking the database node in SSMS and select Tasks - Export Data