How to use DTS in SQL 2008

Question

How to use DTS in SQL 2008

Shaun2012

Hall of Fame

Points: 3214
More actions
January 27, 2009 at 7:50 am

#200521

Hi,
Using SQl 2008, I had exported selected 10 tables from database x from remote server to local server database x.
Now i want to make a DTS package which will take the source from remote server and send to the local server.
Thanks
Sumit

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you sure you don't mean you want to create an SSIS package for that? SSIS pretty much replaces DTS in SQL 2005 and 2008.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 2

Shaun2012

Hall of Fame

Points: 3214

January 27, 2009 at 8:28 am

#933230

Yes,SSIS i forgot in 2008 it is SSIS.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Open up BIDS (Business Intelligence Management Studio), start an Integration Services project, and drag your data transformation object onto the work area. Open that, add your sources, destinations, any modifications, etc., and make sure it's all connected to test/dev copies of your databases. Run it. It should be as simple as that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 4

Hi,

I had started using bids but i am little confused how to build it a small package

in which i have a 2 tables and updating into antother database using in the same server.

i clicked the dataflow and added the source and destination

and linked from source to destination.

Now how should i set that it updates the dsestincation server, if source updates

Can you help me on it.

thanks

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 5

Hi,

I was able to create a package, but one problem i am having.

When i execute the package.

if table 1 has 4 records in server 1

and

table 2 has 4 records which are same

Now if i add 1 record in tABLE 1 AND RUN THE PACKAGE

IT GIVES me

table 2 has 9 records (it copies the full data from that table )

rather than it should give 5 records as well

GSquared SSC Guru Points: 260824 More actions · Answer 6

Ah. There are more requirements than just copying data. Got it.

You'll need to look into more of the options SSIS has for merging data without duplicating existing data. That can begin to get complex.

Hate to say it, but your best option to get that done fast and right is to hire someone who knows SSIS. Otherwise, you're going to need to dig into Books Online and the help files a bunch, and just hope you get it all right.

I don't know any SSIS experts, personally, but I bet you can find one if you decide to go that route. It is your best bet at this point.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 7

Hi,

Thanks can u refer me any good book which can be helpful for this issue or coming problems.

thanks for responding.