CODEWITHSUNDEEP
etldtsinformatica-powercenter
Larry Watanabe
Larry Watanabe

Reputation: 10184

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

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.

Upvotes: 3

Views: 5248

Answers (4)

ETL Man
ETL Man

Reputation: 255

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.

Upvotes: 3

Vishal Shah
Vishal Shah

Reputation: 31

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.

Upvotes: 3

duffymo
duffymo

Reputation: 308928

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.

Upvotes: 4

John Saunders
John Saunders

Reputation: 161791

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

Upvotes: 1

Related Questions