DTS vs. SSIS vs. Informatica vs. PL/SQL Scripting
Asked Answered
A

4

3

In the past, I have used Informatica for some ETL (Extraction Transformation Loading) but found it rather slow and usually replaced it with some PL/SQL scripts (was using Oracle at the time).

(questions revised based on feedback in answers)

I gather that DTS was Microsoft's ETL tool prior to SSIS.

  1. Would it be difficult to convert an existing application using DTS to SSIS?
  2. Given that SSIS is a Microsoft tool and tightly integrated with SQL Server (virtually a part of it) are there any drawbacks to using it? I don't see any efficiency issues, since I imagine that you can do anything in SSIS that you could without it with regard to ETL.
Apocarp answered 24/12, 2009 at 2:56 Comment(0)
A
4

I believe SSIS is Microsoft's ETL tool today, replacing DTS.

It's important to remember that ETL performance has as much to do with your schema and how you're doing the transfer as it does the tool. For example, if you've got indexes they'll run slower than if you do a bulk transfer and create the indexes after it's done. If you do a large batch all at once you're creating rollback logs that increase in size and slow the process down. It could be that smaller batches will run faster, because the rollback log doesn't have to be as big.

Don't give in to the knee-jerk reaction and blame the tool. Look critically at how you're doing it to make sure that you're not shooting yourself in the foot.

Astonish answered 24/12, 2009 at 3:15 Comment(0)
T
3

That's correct, DTS was MS tool for ETL prior to SSIS. While I have never seen DTS before, I believe SSIS is much more user friendly and GUI based in comparison to DTS. Speaking of user-friendly, my first experience with ETL was with Informatica, and I strongly believe that the user-friendliness of Informatica beats SSIS. Inudstry does recognize Informatica to be much more stable and advanced as opposed to SSIS.

Tangelatangelo answered 6/1, 2010 at 4:22 Comment(0)
W
3

SSIS has got it's problems

Does not work with Excel correctly (because of mixed data types, well known problem)

Does everything in the memory = you need a lot of memory.

Especially for sorting large files.

You cannot specify which algorithm to use for sorting.

For example it would be nice to be able to use Merge sort because does not require a lot of memory.

Wersh answered 16/12, 2010 at 0:6 Comment(0)
L
1

Your information is badly out of date. The current Microsoft ETL tool is SQL Server Integration Services (SSIS).

Luciolucita answered 24/12, 2009 at 3:15 Comment(2)
Thanks for the update. I actually didn't come across DTS while looking for an ETL tool - conversely, I looked up DTS and found it seems to be an ETL tool.Apocarp
In SQL Server 2000, Microsoft had DTS, a bad hack. It wound up being used as an ETL tool. It became so popular, that the created a product, SSIS, to meet the same requirements, and much more. Beyond that, there's little comparison between the two.Luciolucita

© 2022 - 2025 — McMap. All rights reserved.