February 11, 2009 at 3:21 pm
I am looking into migrating my data warehouse from Oracle to SQL Server. The warehouse was initially loaded through a bunch of text/control files through SQL Loader.
I can use SSMA to move the data from Oracle to SQL Server but ideally I want to convert the Oracle control files to BCP with format files that I can then use to load the data directly into SQL Server.
Is there a utility to convert Oracle control files to BCP format files?
Thanks!
February 12, 2009 at 10:53 am
Assuming you are talking about converting Oracle "SQL*Loader control files" into something SQL Server would understand to help you run ETL processes... as far as I know, No.
You are gonna have a lot of fun rewriting the code -as always happens during such migrations.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 12, 2009 at 1:57 pm
[font="Verdana"]Check out SQL Server Integration Services (SSIS). You can use this to export the data into files from Oracle, and then load the data into SQL Server from the files.
It's a lot more powerful than SQL*Loader or bcp. Worth learning for this sort of thing. And you can build up a package of loading scripts within it, and then assign it to a job, so you can refresh your database conversion as you go. So you don't lose any of the scripting benefits of SQL*Loader or bcp.
[/font]
February 13, 2009 at 2:32 am
Bruce W Cassidy (2/12/2009)
[font="Verdana"]Check out SQL Server Integration Services (SSIS). You can use this to export the data into files from Oracle, and then load the data into SQL Server from the files.[/font]
If I got it right poster is not asking how to move data from Oracle to SQL Server, poster is concerned about SQL*Loader control files used to load text files during some sort of ETL process.
Bruce W Cassidy (2/12/2009)
[font="Verdana"]It's a lot more powerful than SQL*Loader or bcp. Worth learning for this sort of thing. And you can build up a package of loading scripts within it, and then assign it to a job, so you can refresh your database conversion as you go. So you don't lose any of the scripting benefits of SQL*Loader or bcp.[/font]
I totally agree SSIS is a powerful too but at the end of the day you are going to have to rewrite your ETL processes from scratch as I posted previously 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 15, 2009 at 2:21 pm
[font="Verdana"]The poster mentioned that they are migrating their data warehouse from Oracle to SQL Server. So yes, I would agree that rebuilding their entire ETL would be a requirement.
My points on SSIS stand. If you have to rebuild your entire ETL, check it out. At some point, migrating the data warehouse will involve moving data from Oracle to SQL Server, so you may as well start with something easier to drive than bcp.
BTW, we have a data warehouse here that was originally Oracle and now runs on SQL Server. If I can make a suggestion, check out a product named Wherescape RED. It's not an ETL tool, but it does include some ETL functionality and will manage your data warehouse environment (as well as being a good data warehouse modelling tool.) It's not expensive and it can save you some serious heart-ache! (And no, I am not affiliated to the company, other than being a user.)
[/font]
February 17, 2009 at 12:00 pm
I guess there is no easy way to do this.
Thanks for the suggestions!
April 10, 2009 at 9:26 am
ms (2/17/2009)
I guess there is no easy way to do this.Thanks for the suggestions!
You are correct, not easy way to do it.
The bright side is that it will look great on your Resume! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 10, 2009 at 10:11 am
There is no way to convert the SQL Loader control files to BCP format files.
Why dont you try out SSIS..
May 1, 2009 at 7:41 am
Vijaya Kadiyala (4/10/2009)
There is no way to convert the SQL Loader control files to BCP format files.Why dont you try out SSIS..
"No way" sounds to me like an unfortunate wording -too absolut.
I agree redoing it as SSIS would be probably a good idea but -believe me - I've seen SQL*Loader tasks converted into BCP tasks.
Either way you have to start from scratch, learn what SQL*Loader was doing then writting it again using a different technology.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply