Hi All,
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Help!!!
Hi Irish
I Don't have an answer for you, but since we are in the same situation as you I thought that I would share my thoughts on this.
At first I tried to use Microsoft SSMA tool to do the migration of both schema and data, but soon I came to the conclusion that it still has too many bugs to be to any help in migrating the data. And according to the SSMA team, they won't release a new version until they have rewritten the current java version to .Net.
So I tried the "Import Data" like you did. But because of the AlwaysUseDefaultCodePage issue and because I need to change the Oracle schema to dbo which I can't do for all tables at once in the import wizard, I have moved to the SSIS package solution like you have.
Creating a Data Flow task 600 time (a little less in my case though) is not what I had planned to use a week for so I will now try to create the SSIS package programatically.
I have found this documentation:
http://msdn2.microsoft.com/en-us/library/ms135946.aspx
about how to create a dtsx package in code which I will try to do the next days.
I'm sorry that I wasn't for much help, but hopefully we can find a solution to our mutal problem together.
Best regards
Claus Pedersen
Claus,
Thanks for the reference. I will try that block of code as well, but I'm not optimitic.
What ever happened to "DTS will be easier in 2005"?
|||Generally I like the new features of Sql 2005 SSIS, but I agree that for tasks like this they still have a lot to do. I don't think that Sql 2000 would be easier.
Did you try to create a SSIS programmatically?
I'm working on it at the moment, but get some strange errors with the connection. It fails with a HRESULT: 0xC0048021 error as soon as it tries to connect to a OleDb connection (to populate columns). I have tried to load a working package in my code and proceed with that, but it still fail.
Best regards
Claus Pedersen
Hi Irish
Have you tried to use the ForEach control?
I have experimented a little with it, and it seems like it could work. There are some problems with update of column names though.
Claus
|||Hey Claus,
I have not had a single oppourtunity to work on this since making the last post.
I agree that SQL 2000 would not have been any less of a challenge. However, based on the documentation I've read this is supposed to be a simple process allowing a full conversion of an Oracle Database to SQL 2005. I've not seen it so far. That's the whole point of my excursion.
I will try the ForEach and see what I get. Just seem like a major pain to have to go through all of the objects piecemeal.
|||I agree. I can imagine a developer on Microsoft Sql server team telling his boss that "it could be worse" which converted to marketing language is "extreemly simple".
Wait a moment with the foreach solution. It might not work anyway because of the different metadata from table to table. I'll keep you updated.
/Claus
|||Run through all of the steps, but uncheck Execute Immediately, and check Save SSIS package. Save it to the file system, and then edit the AlwaysUseDefaultCodePage property.
Or are you saying it won't even create the package for you?
|||Sean,
Yes, that's exactly what I am saying. When I was building this the plan was to create the SSIS Package and then see how I could edit it to apply to a variety of different Oracle Instances and versions.
So, I would change the AlwaysUseDefaultCodePage property if I could, but since the package is never created because of the messages received I am not able to do so.
No comments:
Post a Comment