[SQL2005 DTS] Can you create queries to run after/prior to every row in a table?

[Denaes]

Im trying to convert a huge old database to 2005. I can convert things straight over just fine.

My problem is that the old database had many tables that were joined on multiple Char fields. In addition to not being unique and harder to do queries, it was sucking performance out the window on some queries.

Im looking to convert over a table (TableA) and have SQL Server populate an Identity field (TableAID). Then When I load a table thats related, each row Id query TableA for the keys and insert TableAID rather than the multiple other fields.

I can also do this before hand in the old database with a few SQL scripts, but that would make it a two step process using two different technologies. I cant do it afterwards in the new SQLServer 2005 database really. I want to have the new relationships set up when I convert over to make sure the integrity is up to snuff.

If I did it in the SQLServer 2005 database after the DTS, Id have to have SQL scripts to do the queries, delete fields and then create relationships. I guess thats possible, but just not optimal for converting a few dozen databases.

If anyone has any advice on how to do it, how it would be done another way or even just what the proper terms are that I could search on google, that would be great.

This seems like something that wouldnt be totally unheard of doing, so Im pretty sure its been done and probobly documented somewere.

 

[PlausiblyDamp]

You may want to investigate SSIS (Sql Server Integration Services), the SQL 2005 update to DTS, as this gives far more control and functionality.

 

[Denaes]

You may want to investigate SSIS (Sql Server Integration Services), the SQL 2005 update to DTS, as this gives far more control and functionality.

I believe thats what Im using.

Its a project in "SQL Server Business Intelligence Development Studio" (VS2005 with a SQL Server Project) where I have things flowcharted.

Its just so new, we havnt found any books on it yet (B1 books only so far) and we (us at work) are trying to figure out how to use all the bells & whistles to do what were looking for.

So Im fishing for terminology (it seems a common enough need that it would have a name to it) or documentation really. Someone on another forum mentioned it was a Surrogate Key, which coupled with SSIS did pop up some good Google pages.

I probobly just call it DTS, because thats what it was. They renamed it, but it gets the same thing done, just another way and with more control. Just the first signs of aging... like my grandparents calling every video gaming system the Nintendo.