DTS' Other Uses
One of the nicest things about MS SQL is DTS. If you are a reader who has a visceral reaction to anything nice being said about Microsoft you should grab a paper bag so that you don't hyperventilate as we go forward. Shallow breaths... shallow breaths.... ok - ready? Here we go. I suppose that most developers are exposed to DTS (Data Transformation Services) as an import-export mechanism for Microsoft SQL Server. The most common use is during deployment, rehosting or setting up a development environment. There are a host of other things for which you can use DTS that perhaps you hadn't thought of. For example:
- Moving Data from one DB platform or format to another - For example, with 2 ODBC connections to 2 Oracle servers you could import and export data from one Oracle server to another. I know that those of you with experience in Oracle will gasp with horror at this though - but migrating data around in Oracle is unnecessarily difficult (as is just about everything else about Oracle). You could transfer data from a DBase file to an access file. You could move information from a proprietary platform running on Cobol (as long as you had a driver) into a flat file or into MSSQL. It's a very nifty mechanism with all sorts of possibilities.
- File Drop Import - We use DTS to "pickup" a file who's name may not be consistent. It is even possible to FTP a file for import.
- Data Column Validation - With a minimal amount of scripting you can check values prior to importing them. So, for example, you can verify if a field is a number or is parsable into a date, or is populated or not.
- Complex SQL Tasks - You can add SQL task and even COM tasks to a package extending it well beyond just transferring data.
For example, we have a task that imports stock data. It is set to run every weekday. On days that are holidays it would run and sometimes create problems with the Bid and Ask. We needed a way to check and see if it was a holiday. If it was a holiday we wanted to terminate the process. Here is what our DTS Guru came up with. Using the package designer, at the beginning of the package add an ActiveX task that looks like this (this is the generic version).
Function Main()
Dim cn
Dim rs
Dim Flag
Set cn = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
cn.ActiveConnection = "Provider=sqloledb;Data Source=database;Initial Catalog=database;User Id=username;Password=password;"
cn.CommandType = 1 'adCmdText
cn.ActiveConnection.CursorLocation = 3 'adUseClient
cn.CommandTimeout = 60 'set to 1 min
cn.CommandText = "select * from [table]"
Set rs = cn.Execute()
If rs.RecordCount > 0 Then
Flag = "F"
Else
Flag = "S"
End If
Set rs = Nothing
Set cn = Nothing
'msgbox "Result=" & Flag
If Flag = "S" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Make sure and set the task to "continue on success". The last few lines tell the story. If "Main" is set to the constant DTSTaskExecResult_Failure then the task will not continue. The result is a DTS task that is "smarter". We are able to put the execution of the task in the hands of the stakeholder of the site who only needs to be sure and maintain his table of trading holidays.
Dim cn
Dim rs
Dim Flag
Set cn = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
cn.ActiveConnection = "Provider=sqloledb;Data Source=database;Initial Catalog=database;User Id=username;Password=password;"
cn.CommandType = 1 'adCmdText
cn.ActiveConnection.CursorLocation = 3 'adUseClient
cn.CommandTimeout = 60 'set to 1 min
cn.CommandText = "select * from [table]"
Set rs = cn.Execute()
If rs.RecordCount > 0 Then
Flag = "F"
Else
Flag = "S"
End If
Set rs = Nothing
Set cn = Nothing
'msgbox "Result=" & Flag
If Flag = "S" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
4 Comments
-
Isn't it interesting how visceral those responses can be? Microsoft is no different from any other prolific software vendor. They put out some really good stuff (SQL Server, Visio, even IE back in the day) and they put out some garbage (FrontPage leaps to the front of the stack in this category for me, Windows ME, etc.).
I've never been able to understand why so many people want to personify a company to such a degree that they object on some idealogical level to really good tools. -
I'm one of those visceral people. Why? Because developing in DTS is about as fun as running a marathon. Both get you from point A to point B, and both are very difficult, painful, and slow. Need to change the connection info in 100 DTS packages? Sorry, no easy way to do that. Need to find all DTS packages that contain a string? Sure, manually open each package and task, one at a time. Need to have variable that is used in 100 tasks? Use a data table, DTS doesn't have variables. Need to edit the SQL in a task? No problem, we'll gave you a tiny window that you can't resize.
That said, I have heard a lot of good things about SSIS, the replacement for DTS in MSSQL 2005. I can't /wait/ to get rid of DTS and move to SSIS! Oh, but wait. We've got to recreate all of our DTS packages from scratch...I guess I'll be waiting a while. -
I'm not sure that's what Mark meant. I *know* it wasn't what I meant. :-)
Speaking for myself, I was talking more about the cause of the reaction than the nature of it, I suppose. The people who either ignore, dismiss or, too often it seems, violently object to a product because it ships with the MS name attached. Too often there's no merit for the response that's elicited.
Your reaction, while visceral, is based on a real problem that is caused by this particular product. I like SQL Server and have used DTS, but have not found it to be terribly scalable or extensible. To be sure, the interface is lousy. It's a good solution, but usually not my first choice. -
I agree Rob, and I've been known to do that myself. However, I've seen the same thing happen on the other side of the coin. Take DTS, for example (since we're on the subject). Microsoft released what I deem to be a very clunky, half finished ETL product. Can you do a lot of cool things with DTS? Yes, but it's not very fun to do. If you just need a small, simple ETL task that you are not going to save, DTS is great. But for long term projects, DTS is very difficult to work with.
All that said, there are a lot of Microsoft fans that seem to jump at the chance to use another Microsoft technology, even if it has a lot of problems and is difficult to use. And even if there are much better alternatives, they don't want to hear it. They refuse to even consider using a non-Microsoft product. And they get very visceral if someone makes an alternative suggestion.
Case in point: I attended a business intelligence class where the Microsoft instructor told us that Reporting Services is difficult to use for the developer. However, even though this /Microsoft/ instructor warned us about the difficulty of developing in RS 2000, a co-worker of mine just had to use it for a project. He spent about 2 weeks just getting the service to run on the server, and then he spent even more time trying to get reports to work the way he needed (and he never got it to work just right). I just shook my head and laughed, because he is adamant about using Microsoft products, even if they cause you a lot of pain.
I know that Microsoft has a lot of good products on the market, I'm not trying to say that all MS stuff is crappy. But I think it's wise to research the alternatives, and yes even choose non-Microsoft stuff where appropriate.
Sites to Watch
Aggregators
RSS
