DTS Package not selecting all the data in the query

[mit99mh]

I'm migrating DTS packages that copy data from Oracle to Sql server using the Microsoft Ole DB for Oracle provider - the package then only selects part of the data with data of type varchar2 missing - on the other server the package runs completely - has anyone else experienced this before?

Any help appreciated.

 

[ClaireHCM]

In DTS package data transformation task, in stead of selecting the table name, you could replace the table name with select query. In the select query, try to convert varchar2 to some other data type that could be recognized. This is often an issue while migrating data in between different RDBMS. Please play around with different data type conversion and see whether the data got picked up.

 

[mit99mh]

Thanks for the reply - I found a solution:

I had to change the NLS_LANG registry key on the database server hosting the job / DTS package. This was because part of the query ( third party code created for discoverer) used a join that checked the language setting for the session. This was an English setting (ENGLISH_UNITED KINGDOM.XXXXXX) which did not work correctly but if the key was changed to AMERICAN_AMERICA.XXXXXX the query returned all the data - I had a Oracle DBA explain why the SQL failed hence the details were a bit sparse!