Problem extracting data from Oracle > MSSQL

[yeewaitu]

Hi All,

Need your expert advice on this. I am trying to extract data from oracle > MSSQL. The extraction was done using ODBC. It works for some tables except for one table which gave the following error as shown below.

-----------------------------------------------------------
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
-----------------------------------------------------------

Note that the field definitions type extracted from the table which did not work were the same the those other tables that worked.

Does anyone know how to resolve this error?

Appreciate your assistance. =)

 

[gradley]

It looks like it may not like one or more of the records you are importing.

Go to the "Options" tab of the "Transform Data Task Properties" window for this import. You can specify an exception file and other options for trapping errors that may help to determine which record(s) is failing. The file will tell you which records failed and prints out the record.

 

[JayKusch]

What I would do first is open your "Transform Data Task".

In the Options tab, UnCheck the 7.0 Format.

Check the Error text, Source Error rows and the Dest error rows boxes.

Enter a desired filename, ie MyErrorFile.txt, in the Name field of the Exception file area.

Now execute the package again.

In the folder where you set the error log to be created we now "may" find 3 files in there. There will be the MyErrorFile.txt and possibly a MyErrorFile.txt.Source and a MyErrorFile.txt.Dest file.

The MyErrorFile.txt.Source will contain the record(s) that came from the Source, Oracle, system.

wThe MyErrorFile.txt.Dest has the record(s) as they would look like when being inserted into the Destination, SQL Server.

The Source and Dest record(s) may not look alike if you are doing some type of Look Up conversion or other transformation but at least you may be able to see what the issue is with the source record.

Thanks

J. Kusch

 

[yeewaitu]

hi. I have done what you have asked and I am presented with the following message in the exception text file when the table extraction failed:

-------------------------------------------------------------
Error at source for Row Number 1
Errors encountered so far in this task: 1. @@SourceRow: Not Available

@@DestRow: Not Available
-------------------------------------------------------------

This error message is not displayed when I run it on another table.

Any ideas what could be wrong? Please help. Thanks!

 

[gradley]

It looks to me like it doesn't see the import file or doesn't recognize the format.

Go back into the "Transform Data Task Properties" window for this import and click [preview] under the "Source" tab. Does this display any records? If so, are you overriding the file name or path at runtime?

 

[yeewaitu]

> Does this display any records? If so, are you overriding
> the file name or path at runtime?

If it does not display any records? Any advice on what to do next?

If it does display, I am not overridding the file name or path at runtime. These 2 databases are separate from one another.

 

[gradley]

You know it sees the file otherwise you would get an "Error Opening File" message.

If it does not display any records, there is something about the format it doesn't like. Double-click the source connection and display the properties to see what the data looks like there.

I know it's a crazy question, but are you sure there are records in this source file?

 

[yeewaitu]

> are you sure there are records in this source file?

I am pretty sure there are records in the oracle database. I have done an Oracle query using the Oracle ODBC Test application and I can see the records there.

is it possible that over at the oracle side, certain privileges were not granted to me? But the strange thing is that I can query from Oracle.

Appreciate your expert advice on thi. =)

 

[gradley]

You may be onto something there with the permissions. I don't know a whole lot about the Oracle side of things. It certainly wouldn't hurt to check with the Oracle DBA to see if anything has changed.

 

[yeewaitu]

ok. some feedback regarding this issue. I have managed to insert some records into MSSQL. Turns out that I could not browse the data I was inserting which explains the strange error that I got.

Anywayz, I created a linked server to Oracle thru MSSQL and managed to browse the data via MSSQL. Afterwhich, I made use of that "browsing" statement and placed that into the DTS and it worked!

Thanks for your help, people. =)

 

[gradley]

Sure thing! Glad to hear you have it working...

 

[yeewaitu]

Hmmm .. but now I seem to have another problem. I get the following error statement below.

Error converting data type DBTYPE_DBTIMESTAMP to DATETIME

I have some error rows in my oracle and when SQL server views the data in the oracle, the error statement appears.

I need to do the following:-

1. Filter out these error rows and pass them to my oracle administrator.

2. If no changes are to be done by the oracle administrator, how can I skip these error rows while letting DTS do its job or extracting those "correct" rows?

Hope someone would help me out. Thanks!

 

[gradley]

If you are using an ActiveX script to process this information through the Data Pump, you can deterine if it's a valid date by using the ISDate() function.

If it's not a valid date, you can bypass the record by setting Main = DTSTransformStat_SkipRow at the end of the script, instead of the default of DTSTransformStat_OK for good records.

This will bypass those records you deem invalid.

Good luck!

 

[yeewaitu]

Hmm. The problem is that at the source, I am using openquery to query the Oracle database.

When I run this query in SQL query Analyzer, I get the "Error converting data type DBTYPE_DBTIMESTAMP to DATETIME" already.

So, when it hits the error row, it would just stop at the course. Any other workarounds for this?

 

[gradley]

First of all, if you plan to use DTS to import this data, why are you using OPENQUERY to read the data, instead of an Oracle Connection in DTS? If you use the Oracle connection, you should be able to use the suggestion I mentioned above.

Secondly, The SELECT OPENQUERY() statemnet you are using in Query Analyzer, Are you trying to insert the data into a SQL Server table at the same time? If so, does the destination table have a required DateTime field? If it does, is the DateTime NULL in the Oracle database for any of the records?

As I mentioned before, I do not know much about Oracle, especially on how the datatypes compare to SQL Server.

I would still recommend using strictly DTS and the appropriate database connections for your solution.

If you still need to use the OPENQUERY() function, try using a CONVERT() function around the date field:

Ex:

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, CONVERT(datetime,mydate) FROM joe.titles') 

-- or
SELECT name, CONVERT(Datetime,mydate)
FROM OPENQUERY(OracleSvr, 'SELECT name,mydate FROM joe.titles')

Not sure if this is the correct syntax or not

 

[yeewaitu]

can you show me how to do the oracle connection in DTS as you suggested above? Thanks!

 

[gradley]

In DTS Designer, there is a toolbox for different connections. You should see one called "Microsoft ODBC Driver for Oracle". It is the one will 2 Yellow staggered looking disks.

Click on this and enter the Server, User Name and Password. You can also select the [Advanced] button if you need additional parameters.

 

[yeewaitu]

That oracle one worked fine and solved all my problems. thx.