Oracle 10g to SQL Server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Oracle 10g to SQL Server 2005

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-09 : 14:01:30

I'm pulling 20 tables from the oracle datawarehouse. These tables have over 10 million records and every table has over 20 columns. Some columns in the tables do not have values and it causes an error when loading the data to SServer (SSIS). It's also very time-consuming trying to find the columns that doesn't have a value as the statements takes forever to run. Any way to work around this or should I just spend a lot of time trying to find out what columns doesn't have a value? Then use something like WHERE COLUMN1 IS NOT NULL in my SQL statement?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-09 : 14:37:06

Load the data first into a staging table, with no constraints.
Then, use a stored procedure to cleanse and verify the data before sending it to production tables.
But....
.....why are you downloading this much data from a data warehouse into another database?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-09 : 16:18:13

The destination column doesn't allow NULL. But some records on the source column are NULL. How can I get around this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-09 : 18:15:01

Can you use filter logic in oracle to avoid null values .

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-09 : 20:11:34

I fixed the NULL issue. I have a new issue, there is a NUMBER data type on the oracle column that is causing an error on my OLE DB Source. How can I fix this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-09 : 20:22:28

Use Float in SQL Server.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-10 : 10:23:37

quote:
Originally posted by blindman

Load the data first into a staging table, with no constraints.

quote:
Originally posted by basicconfiguration

The destination column doesn't allow NULL. But some records on the source column are NULL. How can I get around this?

Load.
The.
Data.
First.
Into.
A.
Staging.
Table.
With.
No.
Constraints.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-10 : 10:29:15

quote:
Originally posted by blindman

quote:
Originally posted by blindman

Load the data first into a staging table, with no constraints.

quote:
Originally posted by basicconfiguration

The destination column doesn't allow NULL. But some records on the source column are NULL. How can I get around this?

Load.
The.
Data.
First.
Into.
A.
Staging.
Table.
With.
No.
Constraints.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Silly boy

He'd have to unload the data first

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources