Need help with DTS Package Maintenance

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joseph Geretz (jgeretz_at_nospam.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 16:55:04 -0400

I'm using a DTS package to perform database upsizing migrations from
Interbase to SQL Server. The gist of it is I use ODBC to connect to the
source Interbase Database and I make a SQL Server connection to the target
SQL Server database. I then use a set of Transform Data tasks to pump the
data on a table by table basis from the Interbase source database to the SQL
Server target database. Pretty straightforward.

But this is presenting a maintenance problem for me.

We have 77 tables in our database. We don't add table that often so it would
be an acceptable burden for me to track any added tables and add a
corresponding Transform Data task for the new table. What's more burdensome
is the addition, deletion / redefinition of columns. As I've found out, the
Transform Data task doesn't do a SELECT * (which would accommodate table
structure changes transparently) rather it does an explicit column by column
mapping. This means that for every structural change we'll need to perform
maintenance on our DTS upsizing package.

This could be a fairly significan burden. I'm searching for advice on how to
reduce this burden. Thanks for any suggestions which you can offer!

- Joe Geretz -

Relevant Pages

  • Idiomatic Expressions to Operating System Architecture: Transforming Rigids
    ... <Subject: Re: Idiomatic Expressions to Operating ... <endpoint during a single session * DATABASE + by ... <statements is a new feature of SQL Server 2005. ...
    (sci.math)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Word 2003/Access2000/SQLSVR
    ... SQL server being where the data is held and this is accessed through a MS ... entire database and that may be say a record for Berkshire. ... "Peter Jamieson" wrote: ... replaced with the first record on the table in use. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)