DTS vs. SSIS vs. Informatica vs. PL/SQL Scripting

Question

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.

Answer 1

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.

Answer 2

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.