RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

DTS, VBA and the Future

As part of today's MacWorld festivities, I was reminded of something.  Office:Mac 2008 doesn't support VBA anymore, and VBA support for the next version of Office for Windows is supposed to be limited to the ability to upgrade scripts to whatever new thing Microsoft has to replace VBA. 

This is in addition to the issue I ran into a few weeks ago, where I was upgrading a system from SQL Server 2005 Standard to SQL Server 2005 Standard x64.  DTS doesn't work on the x64 edition.  Well, it's not supported.  I got it to work, mostly, but it's not supposed to work, and I'm dreading the next service pack that may break it.

Many years ago, I helped write a very complicated system that collected data from many sources, transposed it and loaded it into SQL Server tables for use by OLAP.  The accepted and probably recommended approach would have been to use DTS. Instead of using DTS, the files were collected with xp_cmdshell, parsed with a Perl script, loaded with BCP (this was before the Bulk Insert command happened) and the data was refined with Transact-SQL.   Why did we use this approach? Because it was manageable and looked like it'd last the longest time.

I pity everyone who's developing using SSIS.  I hope you enjoy re-doing all that work.  Make sure you keep all your specs, in hard copy, because who knows what Microsoft Word's going to be able to read by then.

Print | posted on Tuesday, January 15, 2008 2:38 PM |



# re: DTS, VBA and the Future

You pitty everyone that is using SSIS? Just because of what they are doing with DTS? I think you are just being a bit bitter here. The difference between SSIS and DTS is that SSIS is a success where DTS was not. Yes we may have to tweak a few things in our packages as upgrades come, but I doubt we will ever have to redo them from scratch again. What Microsoft has done with the BIDS environment has been extremely impressive. It is now a true development environment which will make it much simpler for Microsoft to allow for upgrades and not have to do a complete replacement. As far as DTS, I did get excited about it at first, but then I used, it was extremely difficult to do anything worth while in it. After a few months of it, I wouldn't touch it with a 10 foot pole.
1/15/2008 10:35 PM | J

# re: DTS, VBA and the Future

Yes, I pity them. When Microsoft decides to rewrite SSIS from the ground up in a few years, those folks will get to do all that work over again. DTS was a bomb. SSIS isn't much better. No clustering support. No obvious integration with source code control systems. No clean way to document it inline, so you rely on out-of-band documentation. Hard to manage, hard to back up, hard to re-deploy (compared to stored procedures anyway).

Yeah, I pity them. They're using a graphical tool to do something that's easier to do from a script instead of taking the couple of weeks required to learn how to script.

SSIS, like DTS before it, is in the same mold as all the 4GL's that were big when I got out of college. They all featured drag-and-drop functionality, were easy to learn, and turned out to be low on functionality. At least they were "cross platform", you know way back when everyone was on one platform anyway.

There is a sweet spot. If you write at too low a level, your code is obsolete with every version change. If you write at too high a level (write as in "drag little components around and fill in boxes") then your code is obsolete when the box-dragger becomes deprecated. The best place to be is somewhere in the middle, using building blocks that have been around, largely unchanged, for 15 years. Because tools that have been around that long (like BCP, format files, Perl, the "copy" command, and so on) aren't going anywhere.

1/16/2008 8:48 AM | rocketscientist

# re: DTS, VBA and the Future

I am doing the DTS/SSIS thing right now. I also am interested in alternate solutions as you suggested. Do you have any references or links I might investigate?

1/16/2008 10:48 AM | keith
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET