Getting Started with SSIS Script Tasks and Components

This session was an introduction to the scripting objects in SSIS, and how they can be used to extend the built in functionality. Download the files here.

Testing Data and Data-Centric Applications

This session was on testing data-centric applications, both during development and how you can continue validating your data in production. Download the files here.

Thanks again to eveyone who attended!

When I get home, I’ll be jumping straight into a car with my family and driving for 7 or 8 hours. Why, you ask? To get to Birmingham, Alabama for SQLSaturday #81 on 7/30. I’m giving two sessions, Do More (ETL) with Less (Effort) – Automating SSIS and Handling Advanced Data Warehouse Scenarios in SSIS.

The following weekend, 8/6, I’ll be in Baton Rouge, LA for SQLSaturday #64, delivering the same sessions. If you happen to be attending either one, please look me up.

Pre-cons like this are some of the most cost effective training you can get – plenty of time to both cover a topic from end to end, and to dive into the real implementation details that are often missing from shorter presentations because of the time constraints. I hope to see you there!

Data Warehousing with SSIS Deep Dive

Want to learn more about implementing data warehouse ETL with SQL Server Integration Services? Attend this full day seminar, and we’ll cover using SSIS for data warehousing in-depth. You’ll learn everything you need to know to populate your data warehouse with data. We’ll cover how to develop a common framework for your packages, automate the creation of rote packages for staging data, implement common patterns for handling various types of dimensions and fact tables, and how to instrument your packages to identify and recover from failures when loading data. We’ll be using the AdventureWorks databases for the examples, so bring along a laptop configured with SQL Server 2005 or later, and the AdventureWorks sample databases installed. We’ll also cover how the upcoming Denali release of SQL Server affects what we discuss in this seminar.

• Laying out a framework for your ETL
  • Logging
  • Restartability and Recoverability
  • Auditing
• Handling Dimensions
  • SCD Type 1
  • SCD Type 2
  • Advanced Dimension Types
• Handling Facts
  • Transactional
  • Periodic Snapshot
  • Accumulating Snapshot
  • Advanced Fact Patterns
• Errors
  • Handling Processing Errors
  • Handling Data Errors
  • Recovering from Errors
• Best Practices for Managing Your ETL

If you’ve seen my posts about using Biml to generate SSIS packages, and you’d like to learn more, then please check out “Do More (ETL) with Less (Effort) – Automating SSIS”. I’ll talk about the Biml support in BIDS Helper, as well as other approaches for creating SSIS packages without all the manual effort. In my other presentation, “Tuning Analysis Services Processing Performance”, we’ll look at some of the common performance problems people encounter with Analysis Services, and how to resolve them. We’ll also cover a process for doing the tuning.

I really enjoy presenting at SQLSaturday events, and I’m looking forward to this one. I hope to see you there.

Do More (ETL) with Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks or packages that adhere to a pattern, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

Tuning Analysis Services Processing Performance

You’ve got your Analysis Services cube created, and deployed in production. However, you notice that every night, the cube is taking longer and longer to process, and users are starting to complain about their data not being ready when they arrive in the morning. If you’ve found yourself in this situation, or want to avoid being in it in the first place, come to this session. We’ll cover how to benchmark processing performance, track down bottlenecks, and how to tune things to get the best performance for processing your cube.

I’ve attached the slides from the presentation. I’ve also attached a few definition files, one for a Performance Monitor data collector set, and two trace templates, one for SSAS and one for SQL Server. Feel free to customize these files as necessary for your own tuning efforts. Also, while these trace templates can be used in Profiler, the best thing to do is to generate the scripts from them to run the trace directly on the server – lower overhead and less impact on performance.

The file is located on my SkyDrive.

The abstract for my presentation is below. If you happen to be attending the event, please stop by.

Do More (ETL) With Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

Varigence will have a table at the event. If you are working in Microsoft BI, I encourage you to stop by and take a look at what we are doing – it’s very cool.

Do More (ETL) with Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

***If all goes well, you may even get a sneak preview of some exciting new BIDS Helper functionality at this session ***

Handling Advanced Data Warehouse Scenarios in SSIS

So you’ve used SSIS to populate a simple star schema data mart, and everybody’s happy. But now you have new requirements that require more advanced data warehouse approaches, like late arriving dimensions, bridge tables, parent child dimensions, and Type 3 or Type 6 slowly changing dimensions (SCD). How do you handle those in a scalable, efficient way in SSIS? This session will present some common patterns for handling these scenarios. You’ll learn when to use each advanced approach and the pros and cons associated with each pattern. You will learn how to implement these patterns in SSIS, and how to tune them for high performance.

I’ll be presenting on “Processing Flat Files with SSIS”, which goes over some tips and tricks for working with flat files. It’s always a fun session to give, as everyone seems to have encountered some troublesome flat files at some point in their career.

If you are in attendance at the event, please feel free to stop by and say hello.

One item to note – in the presentation, I said that the SQL Azure Data Sync tool only supported syncing SQL Azure databases – no on-premise SQL Server. That was correct at the time, but Microsoft has now announced that CTP 2 for Data Sync will support on-premise SQL Servers, meaning it’s a much more viable option for moving data.

The presentation is available here.

My recap on SQLSaturday #49 is waaay overdue (sorry, Jack and Andy). It was nice to get a chance to present at the home of SQLSaturday, and I have to say, the amount of experience they have in doing SQLSaturday events really shows. The event ran smoothly, and they had plenty of helpful volunteers to help in the appropriate places. I missed the speaker dinner, but heard it was good. There was also some excitement Friday night, when the hotel fire alarm when off at 3:30 AM. Not a pleasant wakeup experience. You could tell the SQLSaturday people who were staying at the hotel though – they were the ones in pajamas with laptop bags over their shoulders.

The event was well attended with 270 attendees and the sessions I attended were pretty full. My presentation was on SQL Azure and loading data to and from the cloud with SSIS and BCP. Fortunately, I was able to get a good internet connection (some other presenters had warned me that reception was a little weak in some rooms), so the demos ran as expected. The after-party was nice as well. I had the chance to catch up with some friends (thanks for the ride, Eric) and meet some new people from the area.

SQLSaturday #56

SQLSaturday #56 – BI Edition was this past weekend. It was another good event, and had about 215 attendees. I got to spent some time with Greg Galloway (one of the other developers on BIDS Helper), Tim Mitchell, and I met several new people as well. The event itself was held at the Microsoft campus in Dallas, and rooms were very nice. We did have a couple of tornado scares, the first of which came up just as my first presentation was starting. It’s a little disconcerting hear an announcement that everyone should proceed to the storm shelters two sentences into your presentation. But as it turned out, the presentation room we were in was a “tornado safe zone” (which I guess means safer that the surrounding rooms, which had lots of glass windows), so the presentation was able to continue. The event had a small number of volunteers, and it was nice to see speakers and attendees pitching in to help wherever needed, whether cleaning up or helping direct people to the appropriate rooms.

A Little Different

Something struck me about both these SQLSaturday events. They were both felt relatively low key and relaxed. I wasn’t involved in the organization for either of these, but I got the impression that the organizers felt that they wanted to provide a good event without going to extremes. They realized that not everything has to be perfect or prearranged to have a good event. At some other events, I got the distinct impression that the organizers sat down and said “How do we make this SQLSaturday the best one ever?”, really focused on setting the bar higher, and probably experienced a lot of stress in the process. Don’t get me wrong – I think improving the SQLSaturday experience is important, and continuing to try new things to improve that experience is good. I’ve really enjoyed those events that changed my concept of what a SQLSaturday could be.

However, some of the groups that have done the “higher-end” SQLSaturday events have had a lot of sponsors (which means a bigger budget) and a lot of volunteers (which means spreading the workload a lot more). For those of you considering organizing a SQLSaturday in your area, I think it’s important to know that you can organize a very successful SQLSaturday on a smaller budget and with fewer volunteers. If you focus on providing what the attendees are there for (focused, relevant content that helps them learn), the rest of it tends to sort itself out. Based on the speakers and attendees that I’ve talked to at various events, there’s not a significant difference in the satisfaction levels between events. Attendees are very happy to get free, quality training. Speakers are happy to have an audience. The other stuff (meals, giveaways, personal assistants for every attendee ) is secondary.

So, if you are considering a new SQLSaturday event, don’t worry about how it will compare with the one the next state over, or the one you gave last year. Most people will be very happy however you choose to organize it. And if someone refuses to attend because there won’t be free doughnuts, well, did you really want them there in the first place?