DTS what is it? (1 Viewer)

I have set up a few DTS packages - say move data from Table1 db A to Table1 db2.

Could I not write a sp to do the same - what are the advantages of DTS??

Thanks in advance.

Data Transformation Services. Dead handy for creating packages to repeatedly Import/Export (and fiddle around with) data.

While you can do some of the things that DTS does in a Stored procedure, you can use DTS to define numerous different work "modules" (which can include Stored Procedures), package them all up into a single job and then schedule that job to run when you want.

I have, for example, a DTS package that deals with all my Active Directory Analysis and uses a combination of vbscripts, stored procedures, import and export routines and mail notification of success/failure all in one single location which manipulates data from AD (duh), two separate databases that use that information and an excel report that is spat out and mailed at the other end.

It takes a bit of time to get your head round what it can do to make it work effectively, but it is a very handy tool to have once you get to grips with it and can take a lot of the leg work out of otherwise repetitive tasks.

You can also call a DTS package externally so if you have a lump of code that produces something that needs to go into a DB and have stuff done to it you can define all of that, including the import, in a single DTS package and call that from your external code rather than have to call each element in turn.

If you search "DTS Basics" in BOL, you'll get a far more coherent overview of what you can do with a DTS package than my explanation.

Heya,

Have a look at http://www.sqldts.com/, there are lots of examples of DTS packages with detailed explainations and when you come to programming some more complicated DTSs you will find this site very useful.

Cheers both of you - I will have more of a read.

The two problems I currently have is - one - I only want to execute the package if a field in the target db is set to yes. How do I do that - within the package itself of can I conditionally run my scheduled execute.

2) I am having problems with the workorder. (Maybe problem one should be a task - which cos its not yes - then task 1 fails and the next tasks do not execute?)

Thanks

Sorry to invade this post but I was wanting the same info and an answer to my question regarding DTS.

I currently have taken over this DB in new role and these is processing of data in VB and was wondering whether a dts would be more effective?

What kind of things is it used for?

The current db looks for fields and replaces them if with some select case statements?

Can select statements be produced in dts?

Thanks

I currently have taken over this DB in new role and these is processing of data in VB and was wondering whether a dts would be more effective?

Click to expand...

Possibly, it depends on what your VB is doing. DTS isn't a replacement to something like VB,but can centralise a set of specific tasks into one place in your database. If your VB populates a table, calls a bunch of storedprocedures then chucks some information out into Excel then it is possible that it might make more sense to convert it to a DTS package. You aren't limited to just importing, exporting and running SQL within DTS. You can have a VB script, for example, called as part of your package.

What kind of things is it used for?

The current db looks for fields and replaces them if with some select case statements?

Can select statements be produced in dts?

Click to expand...

The clue is in the name: Data Transformation Services. it does exactly what it says on the tin.

It is very handy for producing a package of related tasks that you undertake repetitively and normally either takes information into the database or produces output at the other end. The bulk of my import routines normally result in me doing something to that data once I've imported it. Rather than having to import it, run this SP, that SP, go into that other db and run another couple of SPs then export some results or other into Excel for the managers to look at I just run the single DTS package which does all those steps for me.

You can use "raw" SQL within DTS if you want to though I prefer to exec a stored procedure where I can.

You can call DTS packages externally from VB for example. This might make more sense in some instances. I prefer to have the DB logic within the DB rather than the application and it is arguably easier to amend a DTS package on the server rather than have to amend your application because something has been altered in the way the information is placed into your db.

Again, in the first instance I'd suggest searching "DTS basics" in Books Online

Good post tehNellie,
I agree with everything you've said here and I have nothing additional to add.