sql server dts:
Hello again, everyone!
I'm attempting, once again to migrate data from an Oracle database to MS SQL.
So I fire up DTS. I use an ODBC provided by Oracle (the MS one doesn't see
all the tables/schema in Oracle, so I MUST use this) to select the source,
and select the MS SQL database for the destination.
Oddly enough, there is no way to tell DTS how you want the tables named,
using something along the lines of regular expressions. For example, let's
say a table exists in Oracle called Table1, and it's in an Oracle schema
called Schema1. Oracle shows this as Schema1.Table1. DTS also recognizes this
when selecting the tables to move as being "Schema1"."Table1".
However, when DTS goes to create the table in the database, it creates it as
[Destination].[dbo].[Table1]. There's no way, to say, prefix the the table
with the Oracle schema name, so SQL would automatically create it as
[Destiniation].[dbo].[Schema1_Table1].
So anyway, this requires you to go down the list of tables manually and copy
and paste the prefix you want in EVERY.. SINGLE..TABLE you want to move. When
you have well over 500 tables to move, it get's quite tedious. So I decided
before I execute the DTS package, I'd save it to SQL Server in case something
went wrong.
So I uncheck Run Immediately, select the option to save it to SQL server,
and give it a name. It saves the package. Great! Now I go to Data
Transoformations, click on the Package, and Execute.. I get the following
error:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Desciption: The specified file is not a Package Storage File.
I can think of absolutely no reason why this happens when I do this. I tried
using the same ODBC, doing the same thing, on a smaller list of tables, and
it worked flawlessly. However, it seems there is some sort of threshold that
it just doesn't like to surpass.
Can anyone tell me why this happend, how to fix it, or another way to do the
same thing in less time?
