Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: 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.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: 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.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave