THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Enjoy Another Sandwich -- Kent Tegels

Yummy slices of SQL Server between slices of .NET and XML

What The SSIS Masters Know, Part 1

Hello world and yes, I am alive. I have finally caught up enough to begin regularly blogging about SQL Server again. It is good to be back!

As you may know, I will be giving a talk at this year's Heartland Developers' Conference. ( My presentation in entitled "What the SSIS Masters Know." I picked this topic since I feel that many developers are not aware of power that the SQL Server Integration Services (SSIS) offers them. I have also seen many developers struggle with learning curve. As with any powerful tool, there is bound to be a fair amount of complexity. In upgrading a number of SQL Server 2000 DTS packages to SSIS and reading the major books available on the topic I came to realize just how amazing this tool really can be.

A few months ago, I was having dinner with a friend. She asked, "So what do the SSIS masters know that the rest of us don't?" No answer came immediately to mind so I told her I would have to think about that. She smiled and asked me just to talk. About an hour later -- after our meals had come and gone -- she had written down these bullet points for me:

  • The Zen of SSIS: ELT vs. ETL
  • Understanding Control Flows and Data Flows
  • Static pipeline, dynamic flow
  • Understanding asynchronous vs. synchronous components
  • How to Optimize Data Flows
  • Duct-Tape Scripting
  • Using Configurations

She asked a few more questions, not really wanting to know the answers for herself, but because she knew it would get me thinking about this list. What did I mean by "Zen?" Why does the difference in the types of flows really matter? What problems does the design of the Data Flow present? Where do performance problems come from? Is there a technique for analyzing A Data Flow for potential issues? Why did I get so exciting about scripting? What do the developers ignore but the Database Administrators fret about most?

Thankfully, she is very attentive. And incredibly patient when I go rambling off.

The next day I sent this list in with a little garnish describing the talk as topic submission. The talk got the green light and started thinking about how to present these topics to that audience.

The problem here is that these topics can be abstract. I loathe giving talks like that. Like good writing, a good presentation should show, not tell. It should demonstrate in concrete terms why these things mater to the SSIS developer. In the next post, we will talk about data set and database design that will help us put the seven things that master SSIS developers know into context.

Since many of you will not be able to join us that that conference, I thought I would write a series of blog posts covering the essential parts of that presentation. At the end of this series, I will post a URL where you can down load the presentation and the demo files.

Published Tuesday, October 02, 2007 1:52 AM by ktegels



Avonelle Lovhaug said:

I just registered for the conference, and this session is definitely one of the ones I'm interested in. I haven't worked with SSIS at all yet, just SQL Server 2000 DTS. Will someone like me be able to get something out of the session, or will the audience need to be more up to speed on SSIS?

Frankly, what I'm hoping to see is that SSIS is easier to work with than DTS. I'm not even talking about new tasks or anything sophisticated. I'd like to know that the designer user interface is easier to work with. The things that really bug me about DTS are things like fixed size dialog boxes that it hard to maintain, or cryptic prompts that make it easy to lose your transformations. Or the challenges in moving DTS packages between servers. I work from home, and for much of my development work I can just email changes to my customers. But with a DTS package, getting everything pointing to the correct server/databases again is too much work - I have to go onsite to set things up properly.

Perhaps these aren't things that bug everyone else, but they drive me nutty. If I could see a big improvement in this, it would make it easier for me to push my customers to SQL 2005.

October 2, 2007 11:55 AM

ktegels said:

Hi Avonelle, its great to see that you're still kick'n around.

This is going to be a toughie if you haven't spent a little time with SSIS before. I do recommend catching our local DCC MikeB's webcast on SSIS fundamentals first if you haven't already, it is at:

The UI is... well... different and I'll spend some talk about that and why it is the way it is. I'd love to say that the things that annoy about DTS UI are "fixed gracefully" but many of them aren't. We will talk about configurations that can make the process of porting packages around easier.

I'll be heading down to Omaha on Wednesday. Feel free to come introduce yourself and I'll be happy to chat with you about any questions you might have.

October 2, 2007 12:21 PM

Avonelle Lovhaug said:

Yep, I'm still around. Occasionally I even come up for air and communicate with the outside world.

Thanks for the link. I will give it a look before the conference. You have made me very sad that some of the DTS UI issues are not better. I've been placing all my hopes on SSIS, which is probably why I haven't dug into it as deeply yet - I didn't want to be disappointed!

I should be in Omaha in time for the pre-party Wednesday night. Hopefully I'll run into you there.

October 2, 2007 3:55 PM

SQL Queries said:

Thank you very much for the great article. I had read the other parts also.

July 2, 2008 1:20 AM
New Comments to this post are disabled

About ktegels

Kent Tegels passed away on July 31, 2010. Kent was an Adjunct Professor at Colorado Technical University and a member of the technical staff at PluralSight. He was recognized by Microsoft with Most Valuable Professional (MVP) status in SQL Server for his community involvement with SQL Server and .NET. Kent held Microsoft Certifications in Database Administration and Systems Engineering, and contributed to several books on data access programming and .NET. He was a well known industry speaker, and resided in Sioux Falls, South Dakota.
Privacy Statement