Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SSIS vs DTS - advise please!!!

SSIS vs DTS - advise please!!!

SSIS vs DTS - advise please!!!


Working on SQL Server 2000 and embarking on a phased upgrade to SQL Server 2005. We rely heavily on DTS as our ETL, and from what I have read (bar a few exceptions) SSIS is a much more opowerfull tool.
DTS in my view has always been very drag and drop and user friendly, but last night I read (don't worrie - I was on the train) the following comment re DTS vs SSIS which made me a bit worried.

"DTS is very easy to use and intuitibe tool. Limited capabilities for sources and transformations. Some constructs, such as loops, wre very difficult to implement"
"SSIS is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficlt for non-DBAs to use. Requires programming skills"

I am in the Business Intelligence area of my organisation, and although not a pure DBA, the development and maintenance of a relatively simply data warehouse falls within my remit. I am soon going on a Microsoft Certified Technical Specialist - Business Intelligence certification course which covers SSRS (1.5 days), SSTS (Developing ETL solutions - 2 days), SSAS (implementation and maintenance - 1.5 days), exam 70-445 (last day). But the comparison quoted above are now making me quite nervous. I have medium amount of DTS knowledge and are relatively proficient in T-SQL. Any reason why I will not be able to handle SSIS?

Hertfordshire, England

RE: SSIS vs DTS - advise please!!!

We are just moving from an environment where we ran all our reporting against the OLTP database (requiring virtually no ETL), to an environment where we are reporting against a dimensionally modelled data warehouse.  We are using SSIS as our tool for loading the data warehouse, and my entire team is having to learn it from scratch (without the benefit of formal training).  In the past couple of weeks, we've been able to develop some pretty sophisticated ETL packages on our own with minimal difficulty.  I think the review that you read refers mainly to the power of SSIS - there are many transforms in it that perform some sophisticated stuff.  Even so, most transforms have a development interface that helps you over the rough spots.  I see no reason why someone proficient in DTS and T-SQL with 2 days of formal training would not be able to pick it up.

Best of luck!

- Steve

RE: SSIS vs DTS - advise please!!!

Thanks Steve,

Thats certainly encouraging


Hertfordshire, England

RE: SSIS vs DTS - advise please!!!

Hey eo, is this a 70-445 bootcamp you're going on? would be interested to hear more about it, got a URL you could post?


"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

RE: SSIS vs DTS - advise please!!!

sure i'ts...


...don't be fooled by the inclusion of the word "proClarity" as I phoned and made sure it is purely SQL Server 2005 - BI based, and it is, the "ProClarity" is a left over from their marketing dept who markets the BI trainig all under the "proClarity" banner...

Hertfordshire, England

RE: SSIS vs DTS - advise please!!!

I have read similar type articles comaparing SSIS to DTS were the articles make SSIS to be very intimidating because of the potential for extremely sophisticated processes.  My experience and difficulty of learning SSIS on the job during an actual development process has lead me to the following opinions.

1) SSIS is less forgiving about adding or changing elements after the fact.  You save more time sitting down and planning out everything or as much as you can think of before you ever open SSIS and start building a package.  Small items such as changing a datatype or width of a column can cause numerous problems and require additional time to allow package changes.

2) Data Flow / Control Flow. Learn them and what the intent of each is.  This affects number one.  While you may want to load table X then truncate table z then move data from X to Z you, will require you to know where you can do each step the most efficiently.

3) Know what each transforms do and when it is appropriate to use each.  You can use a Merge Join to add a column to your data flow based on other columns matching or you can use a Lookup (I let you learn what is faster).  Knowing How and what each task does will again help you in completing item 1.

4) Realise that there isn't a task that will always do what you want.  This is the reason for the script task in my opinion by far the most powerful task there is.

5)Research Research Research.  Having read numerous articles, a few I have put links in this forum to, have shown me that while my design works the order of tasks and how tasks are doing can have a significant impact package performance.

6) The "Difficult" to learn tasks always mentioned such as loop containers, I found extremely easy to create after about the 3rd one.  THe more you do it the better you get.  I now have admin tasks on my warehouse that Have lops within loops within loops.

8) The last thing I can mention (forgive me I am 2.5 hours rested after a 21 hour development day) is.  While having the ability to set break points and visually debug packages it isn't always as easy or as intuitive as it seems.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: SSIS vs DTS - advise please!!!

I use both SQL Server 2000 and 2005.  If you use DTS packages on 2005, you need to download Microsoft's SQL Server 2005 Feature Pack and install the Backward Compatibility and DTS files.

You'll find out that you will experience problems with DTS packages.  Bottom line - DTS is going away and is not truly supported in SQL Server 2005.


Posting advice: FAQ481-4875: What should I know before I post?

RE: SSIS vs DTS - advise please!!!

Also remember the ActiveX Script task in SSIS is there for backwards compatibility and is due to be deprecated in a future release of SQL Server.  So you are better off investing the time to convert all your ActiveX scripts to the new Script Task.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close