How can I force DTS to re-generate transformations upon execution of a DTS job?
I know lots of folks have had similar questions, but I've yet to see an answer which is directly applicable to my situation, so I'm going to ask it again.
Some Background:
We're in the process of upgrading from a DOS legacy system. Legacy data is exported (via several nasty processes) to an Access MDB with several tables which have a similar (though not 100% identical) structure to some SQL Server tables used in our new all-singing, all-dancing system.
My DTS job copies data from the Access tables to their equivilant SQL tables, and it works just fine.
Unfortunately the table structures in BOTH the Access source and SQL Server destination are liable to CHANGE ON A DAILY BASIS!
The problem is that transformations previously created may no longer be valid, and I have to go through and manually clear them (so that DTS can automatically create new ones). This is not really practical because eventually the job will have to be scheduled to run overnight, so that the data is ready for the test bed in the morning.
Nasty, huh? But possible? I assume that there's some method in the DTS object model that DTS uses to auto generate the transformations in the first place. If I could just figure out a way to invoke it from an ActiveX script.
For the record, I've already taken a look at the exported VB code for my job, and seen how it creates individual tasks, but what I need it a way force it to read the structure of both tables, compare the field names, and create similar tasks on-the-fly.
Am I being particularly dense or is all of this impossible?
I know lots of folks have had similar questions, but I've yet to see an answer which is directly applicable to my situation, so I'm going to ask it again.
Some Background:
We're in the process of upgrading from a DOS legacy system. Legacy data is exported (via several nasty processes) to an Access MDB with several tables which have a similar (though not 100% identical) structure to some SQL Server tables used in our new all-singing, all-dancing system.
My DTS job copies data from the Access tables to their equivilant SQL tables, and it works just fine.
Unfortunately the table structures in BOTH the Access source and SQL Server destination are liable to CHANGE ON A DAILY BASIS!
The problem is that transformations previously created may no longer be valid, and I have to go through and manually clear them (so that DTS can automatically create new ones). This is not really practical because eventually the job will have to be scheduled to run overnight, so that the data is ready for the test bed in the morning.
Nasty, huh? But possible? I assume that there's some method in the DTS object model that DTS uses to auto generate the transformations in the first place. If I could just figure out a way to invoke it from an ActiveX script.
For the record, I've already taken a look at the exported VB code for my job, and seen how it creates individual tasks, but what I need it a way force it to read the structure of both tables, compare the field names, and create similar tasks on-the-fly.
Am I being particularly dense or is all of this impossible?
