Just recently we came across a client who was currently running their data warehouse on an Oracle 10g installation and wanted to come home to the world of SQL Server. As I am personally not a fan of Oracle at all I couldn’t fault their logic one bit, I had not however ever experienced moving and SAP DS transform from one database server to another for the same datastore and so I thought I’d look into it a little more. Let’s begin by stating the problem a little clearer and throwing up some caveats about what this posting is not.
We have an existing Oracle database which is currently populated by use of an SAP Data Services Transform. To make things easy, from the point of view of this blog, our output table is a template table as this negates the need for us to physically create any new tables as SAP takes care of this for us. In the real world you are very likely to not be using template tables and so you would need to take care of creating the new data tables manually. So in this example, we will be working with a very simple transform which takes data from an input file and uploads it directly into an Oracle data table. We will be taking this transform and porting it to SQL Server with the least possible effort on our part, not just because we’re lazy, but also because least effort means least chance of breaking our transform.
So let’s take a look at our very simple transform:-
As you can there really is nothing complicated here, we have an input file which is just pretty much inserted unadulterated into our ‘THISISATEST’ Oracle database table. Note, let’s not talk about case sensitivity in this blog…. Oracle. As you can see the table resides within the POC datastore and so lets without any further ado take a look at our data store, We thus right click on our POC datastore and select the ‘Edit’ menu item.:-
So let’s take a look at what we currently have defined. A datastore named POC which appears to be defined as an Oracle 10g datastore. Note that the datastore type is NOT editable and so we cannot just change from one data store type to another.
So let’s go advanced and edit the configuration by using the ‘Edit’ button towards the bottom of the screen. You are presented with a screen similar to the following where our Oracle configuration is defined:-
What we need to do in this instance is create a brand new configuration for our SQL server database which will utilise exactly the same data store objects thus allowing us to make the change from one provider to another with practically no work. Lets then ‘Create’ a new configuration by using the button indicated in green above. This will invoke the ‘Create Configuration wizard which is the only way you have of defining a database type for a configuration.
In this instance, I have elected to use an ODBC connection to connect to SQL Server (which you can’t see) and create a new Configuration called somewhat originally ‘SqlServerConfig’. Once you elect to create this configuration the previous configurations screen will show the following:-
Wow.. Ain’t that simple. I now enter my various login credentials which have been obfuscated for the sake of this blog and we are left with two configurations that can be utilised by the transformation. But we want to switch to using SQL Server rather than Oracle so let’s make one small change that will effect this easily:-
All we do is change the ‘Default Configuration’ for the ‘SQLServerConfig’ to True which will mean that this will always be used in preference to the Oracle configuration unless of course you specifically override this behaviour. You can now go ahead and run your transformation and you should see, so long as you made no errors, that the ‘THISISATEST’ table will now have been created within the SQL Server database rather than the Oracle database. And that really is the crux of migrating from Oracle to SQL Server, or indeed from any one database to any other.