DTS- Data transfer from Multiple tables between database

[swetab]

Hi,
I need the best approach that is suitable to transfer the data from multiple tables between SQl server databases.

All I am doing now is using "Tranform Data Task" in between the two sql server connections for each table.And adding Transform data task link everytime for each table.

Is there are better way for this.

Thanks

 

[mrdenny]

IMHO that is the best method to use. Some people like the object transfer task, but I don't because it tries to move securtity, indexes, users, etc. Because of all this it runs much slower.

Also the object transfer task in DTC is VERY hard to troubleshoot if there is a problem. Most of the time it simply says that an error occured. With the data transformations you at least get a little more info than that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[swetab]

I didn't get your reply clearly.

I have created a DTS package which holds sql connection and Transform data task links for each table that needs to be transfered.
Are you saying what I am doing is best approach?

Or is there something I am missing.

Thanks

 

[mrdenny]

Yep, that's the way that I would do it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com