SSIS vs DTS - advise please!!! 1

[eo]

Hi

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?


EO
Hertfordshire, England

 

[FlaBusInt]

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

 

[eo]

Thanks Steve,

Thats certainly encouraging

E

EO
Hertfordshire, England

 

[SQLBI]

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?



Cheers,
Leigh

"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

 

[eo]

sure i'ts...

http://www.trainingcamp.co.uk/courses/microsoft/proclarity/mcts_curriculum.asp

...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...

EO
Hertfordshire, England

 

[MDXer]

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.

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

 

[SQLBill]

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.

-SQLBill

Posting advice: FAQ481-4875

 

[MDXer]

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.

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