Re: Need help with DTS Package Maintenance
From: fn (f_n_a_c_e_r_removeunderlines_at_hotmail.com)
Date: 07/29/04
- Next message: fn: "Re: Name of a task in the package"
- Previous message: Joseph Geretz: "Need help with DTS Package Maintenance"
- In reply to: Joseph Geretz: "Need help with DTS Package Maintenance"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Jul 2004 15:36:32 -0600
Assuming that "a set of Transform Data tasks" means "77 or so Transform
Data tasks" then my approach would be to reduce down to as close to "1"
as possible. That means that code will have to read the source to
destination mapping specifications and configure the task dynamically.
I have found this approach to be easier to implement with the Bulk
Insert Task (which unfortunately will require that the source data be
dumped into text file before being loaded into SQL Server). Bulk Insert
can use an external FMT file to specify the mapping and it can be run as
a TSQL command. So all your code has to do is specify the input file
name and the FMT file name.
Farid
Joseph Geretz wrote:
> 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 -
>
>
- Next message: fn: "Re: Name of a task in the package"
- Previous message: Joseph Geretz: "Need help with DTS Package Maintenance"
- In reply to: Joseph Geretz: "Need help with DTS Package Maintenance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
