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 Exchange Contacts to SQL

I have been trying to accomplish the same thing but have not been able to do
so in SQL. Were you able to accomplish this task?
"Terry" wrote:

> Hi everyone,
> We have a contacts database on SQL. but also a large number of users stori
ng
> contacts within Exchange.
> What I'd like to do is be able to extract Exchange Contact data, maybe on
a
> nightly basis, into SQL.
> What would be the best way to achieve this? Could i use a linked server?
> I've found various snippets of VBA, but nothing that looks like it could
> achieve what i need.
> Any ideas?
>
>http://msdn.microsoft.com/library/d...ado_objects.asp
This lists how to connect to exchange using ADO.
I did this once (and I don't claim this is the right way) to see if it was
possible.
MS access comes with a driver to link Exchange/outlook folders.
You can link the global address list under "address books".
Then import the contacts to SQL Server using DTS.
There are a few different ways I can think of doing this, the above simply
being the easiest (not the right way).
Simon Worth
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> I have been trying to accomplish the same thing but have not been able to
do
> so in SQL. Were you able to accomplish this task?
> "Terry" wrote:
>
storing
on a|||Simon,
Thank you. I'm not major programmer, I have some things but mostly in SQL
and some Active X scripting. Do I have to Visual Studio to use this code in
the link that you provided or can I use this in DTS ActiveX?
Thanks again for your response, I hope I can use it.
"Simon Worth" wrote:

> http://msdn.microsoft.com/library/d...ado_objects.asp
> This lists how to connect to exchange using ADO.
> I did this once (and I don't claim this is the right way) to see if it was
> possible.
> MS access comes with a driver to link Exchange/outlook folders.
> You can link the global address list under "address books".
> Then import the contacts to SQL Server using DTS.
> There are a few different ways I can think of doing this, the above simply
> being the easiest (not the right way).
> --
> Simon Worth
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> do
> storing
> on a
>
>|||Oh another thought,
I want to be able to do this from the Server level, meaning being able to
pull the contacts from every user that is a member of a particular group.
Therefore, I'm hoping that this is something that I would not have log on to
every user's mailbox individually to export their contact folder.
"Simon Worth" wrote:

> http://msdn.microsoft.com/library/d...ado_objects.asp
> This lists how to connect to exchange using ADO.
> I did this once (and I don't claim this is the right way) to see if it was
> possible.
> MS access comes with a driver to link Exchange/outlook folders.
> You can link the global address list under "address books".
> Then import the contacts to SQL Server using DTS.
> There are a few different ways I can think of doing this, the above simply
> being the easiest (not the right way).
> --
> Simon Worth
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> do
> storing
> on a
>
>

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing data from Excel file w/ multiple Excel sheets

Hi,
I want to import an Excel file that has many Excel sheets within it. I need
to get this into an SQL database using the DTS. I have set the DTS task to
import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
What have I forgotten? Can someone assist in getting all sheets to be
imported. I am sure all of you knwo what I mean when I say "all sheets"
imported. However, just in case . . . these are the sheets that have the tabs
at the bottom of the Excel sheet.
I appreciate any assistance rendered.
Thanks.
In SQL2000, you can use the DTS Import Export Wizard. Once you selected the
appropriate file you want to load, it will show all the sheets in the
workbook on the "Select Source Tables and Views". You would be able to edit
the destination name for the tables where the data will be loaded.
If the package is already created you can create the connections and select
the sheet you want in the "Transform Data Task"
"4pcd" wrote:

> Hi,
> I want to import an Excel file that has many Excel sheets within it. I need
> to get this into an SQL database using the DTS. I have set the DTS task to
> import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
> What have I forgotten? Can someone assist in getting all sheets to be
> imported. I am sure all of you knwo what I mean when I say "all sheets"
> imported. However, just in case . . . these are the sheets that have the tabs
> at the bottom of the Excel sheet.
> I appreciate any assistance rendered.
> Thanks.

Importing data from Excel file w/ multiple Excel sheets

Hi,
I want to import an Excel file that has many Excel sheets within it. I need
to get this into an SQL database using the DTS. I have set the DTS task to
import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
What have I forgotten? Can someone assist in getting all sheets to be
imported. I am sure all of you knwo what I mean when I say "all sheets"
imported. However, just in case . . . these are the sheets that have the tab
s
at the bottom of the Excel sheet.
I appreciate any assistance rendered.
Thanks.In SQL2000, you can use the DTS Import Export Wizard. Once you selected the
appropriate file you want to load, it will show all the sheets in the
workbook on the "Select Source Tables and Views". You would be able to edit
the destination name for the tables where the data will be loaded.
If the package is already created you can create the connections and select
the sheet you want in the "Transform Data Task"
"4pcd" wrote:

> Hi,
> I want to import an Excel file that has many Excel sheets within it. I nee
d
> to get this into an SQL database using the DTS. I have set the DTS task to
> import the Excel sheet successfully, but I ONLY get the 1st sheet imported
.
> What have I forgotten? Can someone assist in getting all sheets to be
> imported. I am sure all of you knwo what I mean when I say "all sheets"
> imported. However, just in case . . . these are the sheets that have the t
abs
> at the bottom of the Excel sheet.
> I appreciate any assistance rendered.
> Thanks.

Importing Data From a Flat File

Hi,

I am trying to import data from a Flat File in SQL Server 2005, this used to be a simple task in SQL2K but for some reason isn't in SQL2K5.

I am only trying to import part of the record not the whole thing and it keeps giving me this error:

**************************************************************

MessagesWarning 0x80047076: Data Flow Task: The output column "Column 3" (19) on output "Flat File Source Output" (2) and component "Source - consumer_0_dat" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "Column 3" (19) on output "Flat File Source Output" (2) and component "Source - consumer_0_dat" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

******************************************************************************

If I delete the columns and only have those that I want inserted then i get column length errors.

Can someone pls provide instruction to import a flat file in my database?

Thanks

These are just Warnings, this should not stop your package from execution, unless there is error happening somewhere else.