Just curious, does anyone have any experience with both SQL Server Data Transformation Services (DTS) and BO Data Integrator? Looking for some general high-level feedback on both, especially any comparisons…
Especially curious about the “future” DTS (SQL Server 2005)…
Regards,
Chris
cjweis (BOB member since 2003-10-02)
I use both MS-DTS and BODI, Ultimately what they both do is pretty much the same - Extract, Transform and Load data. They’re a league apart though in how they achieve it.
DTS is very good at straight forward shifting of data (with good programmtic capabilities as well) and whilst it takes advantages of wizards I found that it takes an element of prgramming knowledge to use it fully.
BODI has been, in my opinion, better designed with ETL and Data Warehousing in mind. As an example there are built-in functions to deal with table comparisons and preservation of history which makes the management of slowly changing dimensions and history retention/auditing far easier to achieve.
DTS has one great advantage though - as it’s bundled with MS-SQL so it’s free. If cost is a significant deciding factor then that’s a major benefit.
The next DTS in SQL 2005 (called MS-Integration Services) looks really good. I haven’t had the opportunity to try it yet though.
Gary Nuttall
gpn01 
(BOB member since 2004-11-30)
We have and use both. DI is better at industrial-strength, repeatable ETL. For one-off stuff (like importing an Excel spreadsheet into a table), DTS is easier and quicker.
DI allows code reuse, metadata reporting, and has good performance. It deals with XML data, web services (as source/target/job) and http streams, and so on. Optional components are available to integrate with SAP, Siebel, Peoplesoft, etc.
DI is really a steal. While it’s not as powerful (feature-wise) as Informatica or Ascential, it’s also quite a bit less expensive. While I haven’t seen DTS 2005, we were able to justify the ROI in terms of developer productivity relative to the current DTS.
dnewton 
(BOB member since 2004-01-30)
What do they better in your opinion? I know just a few points, so I am hoping to get you opinion.
Thanks
Werner
Werner Daehn 
(BOB member since 2004-12-17)
I’m relying mostly on feedback I’ve heard from others who are familiar with our current DI tool and have experience with one of those other ones. I’ll try to get some specifics and post them here.
dnewton (BOB member since 2004-01-30)
Sorry, i wasn’t precise. I am working at Business Objects but would love to know what Informatica does better in your opinion. In my point of view DI does pretty much everything a little better except a few things.
Werner Daehn 
(BOB member since 2004-12-17)
Now that I’ve looked at the XI release notes, the gap – real or perceived – appears to be closing. There are better team-development capabilities, wider support for Change data Capture, and a handful of new functions. Informatica seemed to be stronger in these regards in the past.
For us, our data set is smaller (our whole warehouse is < 0.5Tb), so performance is secondary to features/functionality. I’ve been occasionally stumped by functions that I’d expect to be in DI that seem unavailable. (For example just the other day, I needed CHAR/ASCII type functions, that would either return an ascii code for a character, or the reverse, generate a single-character string given an ASCII value.)
I would also like to see more prebuilt application adapters (similar to what is available for SAP). DI could be used not just for Data warehousing ETL, but for EAI (enterprise app integration), if more were available. I understand you can write your own adapter, but we’d prefer not to do that ourselves.
dnewton (BOB member since 2004-01-30)
By the way – in case any of them are listening 
– I’ve been really happy with the level of service from the DI tech support team at BO.
dnewton 
(BOB member since 2004-01-30)