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

[Denaes]

I'm 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.

 

I'm looking to convert over a table (TableA) and have SQL Server populate an Identity field (TableAID). Then When I load a table that's related, each row I'd 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 can't 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, I'd have to have SQL scripts to do the queries, delete fields and then create relationships. I guess that's 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 wouldn't be totally unheard of doing, so I'm pretty sure it's 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)' date=' the SQL 2005 update to DTS, as this gives far more control and functionality.[/quote']

 

I believe thats what I'm using.

 

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

 

It's just so new, we havn't 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 we're looking for.

 

So I'm 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 that's 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.

Edited December 28, 2005 by Denaes