migrate data from sql to oracle using ssis

Question

how to migrate data from sql 2008 to oracle 11g or 10g through SSIS.? I tried import export wizard, but its not working.

Answer 1

What's not working? Any errors?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Please post the errors or a description of "not working" so we can help you better.

But if you want to create a package from scratch, it's relatively easy. You need a Data Flow Task with an OLE DB connection manager to get to SQL Server and possibly an OLE DB connection manager to connect to Oracle (double-check that in Books Online. I don't use Oracle). I don't know what sort of other data transformation tasks you'll need to make the data types compatible with Oracle, but probably a Derived Column Task would do the trick.

---

MCITP:DBA, MCDBA, MCSA

Answer 3

through import export wizard  it shows rows are transferred but when check the warning message it gives as follows:

Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard).

below is the image for reference, as error message shows no rows are transferred i cant see table in oracle, which i have transferred.

Answer 4

Actually, I see warnings (no errors) and 9 rows transferred. Did you check the Oracle destination to make sure those 9 rows aren't actually there?

Warnings don't stop a process from working. Errors do.

---

MCITP:DBA, MCDBA, MCSA

Answer 5

Instead of import/export wizard...you can create ssis package also.in connection manager select Oledb proivder for oracle.if you have any data type issue also we can fix through transformations.

Thanks

CMK....

Answer 6

Yes, definitely, I have checked in oracle there is no any table is transferred from sql.

Answer 7

You can manually create a package to do this for you, or save the package from the Import / Export Wizard and work with that. Either way, I recommend getting your network admin and Oracle admin in on this issue and ask them to sniff the network & Oracle during your next attempt to run this transfer. For some reason, SQL Server is getting back a message that tells it the transfer is good.

Just an FYI: there is no table transfer necessarily happening. Just rows. The data must be going somewhere that you're getting the "9 rows transferred" message. Because if SQL Server couldn't touch that destination at all, you'd be getting big red circles with Xs all over the place.

---

MCITP:DBA, MCDBA, MCSA

Answer 8

It must be the lack of the proper datatype sets in the mapping file. You many need to install additional ones to match the target, or use SQL to convert any to whatever does not break the import, or finally you can simply output data to flat files (e.g. in chunks og 250K records).

---

Arthur My Blog

Answer 9

I tried through SSIS & it shows me rows are transferred, but when i checked it oracle it shows me table or view does not exist. In oracle all permissions & rights are properly set though error comes.

Hence I tried the oracle sql developer software for data migration & it works, i can see table is migrated in oracle. but the thing is that when i use SSIS, oracle is unable to find the migrated table.

Answer 10

I wonder if SSIS doesn't have the proper Oracle drivers. Which version of SSIS do you have installed (including all service packs, etc.)? Which version of Oracle (more specifically than what you listed above) are you using?

---

MCITP:DBA, MCDBA, MCSA

Answer 11

SQL server enterprise edition 2008 R2 with SP1 & oracle 11g also installed oracle attunity

Answer 12

Hi Mayur-DEW,

You must still request access and provide valid credentials to access the information on the Oracle warehouse. The following link instructs how to install and configure the Oracle driver for connectivity:
http://blogs.microsoft.co.il/blogs/bilive/archive/2011/01/16/oracle-driver-installation-for-ssis-2008-r2.aspx

For more information about Oracle with SSIS, please see:
http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

Thanks,
Eileen

---

Eileen Zhao
TechNet Community Support

Answer 13

Hi Eileen,

I have done same installation steps which mentioned in above link. Also I have taken a help from oracle DBA, according to them if DBA permission is assigned to oracle user then all access are granted to him. they are also don't know why oracle dosen't show table if sql shows "rows are transferred"

I can see table in present in oracle by select * from tab; but when i wan't to select the table using select * from my table_name then it gives an error"table or view does not exist".

This error is only for those tables which I have transferred from sql to oracle.

Please help me if any one knows solution.

Answer 14

It would help if we had a little more than pseudo code. "Select * from my table_name" has a T-SQL syntax issue due to the space. SQL Server would read that as table name of "my" with a table alias of "table_name". The "table or view does not exist" error would be resolved by the use of square brackets (in Microsoft SQL Server) to read as "Select * From [my table_name]".

If you're trying to read it from Oracle, I don't know the syntax for the SELECT statement, but I'm wondering if SQL schemas might be translated over in Oracle different than expected. Such as dbo.mytable_name becomes dbomytable_name or something like that.

Does Oracle have an object explorer similiar to SSMS? If so, see if you can browse to those tables without using a query. If you don't have those permissions (or that access), get your Oracle DBA to assist you with that portion of the test.

---

MCITP:DBA, MCDBA, MCSA

Answer 15

syntax is also same there is no any error about syntax as I already mentioned I was involved oracle DBA in this & according to him if DBA permission is assigned to oracle user then all access are granted to him.

I installed Oracle universal driver (client ) all things but there is something which i am missing.

Other wise I have another option of ORACLE SQL DEVELOPER software, its really good for sql to oracle OR oracle to sql server migration.

Answer 16

Hey guys finally I have solved my problem, problem is in oracle. ORACLE DBA helped me to solve the problem.

I note down  answer here might be helpful for others also. After the data is exported to oracle  there is data type conversion happens

hence if you want to select table then you need to specify your table name in double quotes.

ex:- 1) connect to user in oracle,  2) then check table is exist in your current schema using  select * from tab;

      3) select * from "table_name";

finished.