Balanced Data Distributor (BDD) for SSIS

I was just alerted to a Microsoft developed SSIS component that might help in situations where you need to load large amounts of data via SSIS and have some slow components in your Data Flow. The component is called Balanced Data Distributor or BDD and it is available for free from Microsoft for SQL Server 2008/2008R2/2012 in both 32bit and 64bit flavors.

This component was developed as a way to split your processing within an SSIS package to take advantage of today's multi-threaded servers and do it without requiring you to run multiple copies of the same packages in parallel or other more difficult solutions. The component is very easy to use, once installed you just drag in onto your Data Flow and hook it up to your input data, then you can pull off as many output flows from it as you want (Microsoft is currently not recommending more than 5, but you should test it in your environment to see what works best). From there your output flows should be the same to execute the slow moving processes and then they can each write directly to the same output or be merged back together using a Union All depending on your needs. Whatever the number of outputs you use the component will do the splitting by routing each pipeline buffer of data to the outputs in order. So, if you have 2 outputs setup, the first buffer goes to output 1 and the second buffer goes to output 2 and so on until the whole input file is complete.

Nice simple component that can have huge benefits if you are working with data that is just taking way too long to load in a single Data Flow. The downside is that you will need to duplicate the components in each of the output flows, so maintenance might be a mess later on since you will have make changes in each of the flows. Overall, though that issue is probably going to be out-weighted by the runtime improvements you should see in your packages. Also, keep in mind that this is going to be processing the data out of order, so if your process requires that this component won't help you unless you can work around that requirement.

Below are some links on this component:

• Microsoft TechNet video demo
• SQLCAT post
• SQL Server 2012 version download link
• SQL Server 2008/2008R2 version download link (look out for the note about a fix to this version)

Stay tuned for a more detailed post on using this as I do some experiments in my development environment to see just how this component works. I wanted to get this out there as a quick post since I already know of 2 clients that I have worked with recently that will probably benefit from using this component.

SQLSaturday #183 Albuquerque, NM

I'm starting off my 2013 speaking engagements at SQLSaturday #183 in Albuquerque  NM on February 9th. Currently my session, "SSIS 2012: More Than Just a Pretty UI" is scheduled for 4:15pm in Room 4. For those that can, please sign up on the SQLSaturday #183 site. The event is free to attend, but you will have to pay $10 if you want the lunch provided onsite. I have attended many SQLSaturday's over the last few years and have been honored to speak at many as well as organizing the Denver SQLSaturday in 2012 (stay tuned for more news on SQLSaturday #190 in Denver on 9/22) and I think this is some of THE BEST SQL training out there, done by people in the local community and from around the world. All of the speakers and volunteers put on these events in their free time with the help of sponsors to offset the costs.

If you have the time and you are in the Albuquerque, NM area, please register and attend SQLSaturday #183. If you are not in the Albuquerque area, please visit the main SQLSaturday website and see if there is an event in your area!

PASS Summit 2012 - Part 2

Sorry for the long delay in posting my summary of PASS Summit 2012 activities, but I needed to enjoy the Thanksgiving break off from work and travel.

I left off on my previous post on Monday afternoon before the Networking Event that took place around dinner time. The Network Event has been done for many years now by Steve Jones (b|t) and Andy Warren (b|t) before PASS Summit starts and is always a good event to meet lots of people. For me I had overdone it earlier in the day so I wasn't feeling very well, so sorry if you tried to meet with me and I didn't make it around to talk to you. Thankfully the issues I was having passed later in the evening, but I wasn't sure what was going on for awhile that evening.

Tuesday was the first day that I had Chapter Leader meetings to attend throughout the day. First was the meeting with all SQL Saturday organizers where we had many discussions on where we are with SQL Saturdays and where we want to take them in the future. Lots of very helpful information that we will be using for our next SQL Saturday on 9/21/2013! After lunch was the Chapter Leaders meeting where we split up into multiple different groups to talk about specific issues around starting/running/extending our User Groups. Again lots of great info that I will be looking forward to implementing in our group.

Tuesday evening was the Welcome Reception put on by PASS. As usual I was a little bit late getting to this event again this year, so I missed the First Timers entering the party (maybe next year I need to be a mentor to force myself to be there when this happens). The party was big as it has been over the past couple of years, was still able to get to the food and drink without too much waiting though. After meeting quite a few people I headed over with a few of the Pragmatic Works guys to the Volunteer Party at The Garage. Even though I had paid for the Exceptional DBA Awards put on by SQLServerCentral.com & Red Gate, I wanted to attend the Volunteer Party since this was the first year I have been invited to it (hate it when there are multiple parties going on at same time). Had a great time bowling and watching the election coverage.

Wednesday was finally the first official day of the Summit. It started off with Keynotes given by the current PASS President, Bill Graziano (b|t) and various Microsoft representatives. The biggest announcement that I picked up on are the changes to allow Multi-Dimensional cubes to be queried by DAX along with MDX, this puts Multi-Dimensional cubes on par with the newer Tabular models. Can't wait to see how this is fully implemented in vNext of SQL 2012 (this will not be included with SQL 2012 SP1 which was also announced). Also announced were changes to the ColumnStore Indexes to make the updateable and able to be clustered, should add some pretty cool new capabilities. The last thing that I remember from that Keynote was the Hekaton announcements, which is an in-memory OLTP solution that is supposed to add many X improvements in speed for most database solutions (assuming you have tons of memory on your servers). There were other announcements at the Thursday Keynote, but I didn't attend much of that one and heard from others that some of the demos were a bit long, lots of stuff around Big Data/Hadoop.

As with previous years I didn't attend a bunch of sessions during Summit, I end up spending a bunch of time networking and volunteering at the PASS Community Zone and spending some time at the Pragmatic Works booth. It was cool to see the Summit from the perspective of an exhibitor this year, that was the first time I got to see it that way. I did attend a session given by Mark Stacey (b|t) on using NASA data with PowerPivot, which was really cool because he built the whole session around how to get and use the free NASA and ESA data that is out there in demos. Being a Space Nut this was right up my alley and gave me some possible great ideas for future presentations (thanks Mark!). I also saw a great presentation on setting up Kerberos with SharePoint, which as anyone that has done knows this is a real pain point and Chuck Heinzelman (t) laid it out in 7 steps, should help if I do end up running into this in the future. I was able to get to one of the Lightning Talk sessions (yet again in way too small of a room again this year) and saw Bill Fellows (b|t), Jes Borland (b|t), Niko Neugebauer (b|t) and Oliver Engels (t) present on some great topics. As with most conferences with any BI sessions there was a BI Power Hour at Summit again this year and as with the previous ones this did not disappoint in presenting more solutions with Microsoft BI that you probably never would have thought it could do. Great job by Matt Masson (b|t), Chuck Heinzelman (t), Peter Myers, Matthew Roche (b|t), Sean Boon (b|t) and of course Patrick LeBlanc (b|t). I have been to at least 6 different BI Power Hours over the years at various conferences and they are still by far the funniest session to go to. The last session that I remember going to was on the details of the changes to allow for DAX on the Multi-Dimensional cubes, sounds like they did a good job adding these changes in the right way, including no translation to MDX, just adding DAX as a full featured query language against the MD cube.

As with every year there were tons of events in the evenings after the sessions stop, including the Exhibitor's Reception on Wednesday night that I had to try and work as "security" at the Pragmatic Works booth for the 200+ free books that Brian Knight (b|t), Adam Jorgensen (b|t), Mike Davis (b|t) and various other current/past Pragmatic Works employees were signing for free! Was a very popular thing at the reception and we were able to offer many more books to the attendees thanks to Wiley/Wrox being in the booth next to us and having boxes of these books there that they could allow us to give out. After we got rid of all the books at the reception, a few of us headed over to get setup at the Hard Rock Cafe for the Pragmatic Works, Microsoft and HP SQLKaraoke Event with a live band! Was an awesome party and the first time that I have seen karaoke done with a live band, which was really cool. Not sure how we are going to top that party next year in Charlotte, but I'm sure we will come up with something! :)  Thursday evening was the night that we set for the annual Colorado Dinner at PASS Summit. As in past years we had it at Elliot's Oyster House and as always they were very accommodating for a group of 20+ people that were all paying separately and ordering whatever they wanted. After the dinner we caught a cab over to the EMP (Experience Music Project) to catch the end of the Attendee party. I love this museum, in fact I have been there so many times over the years that it is one that I actually went ahead and became a yearly paying member of. I got to go down to the Science Fiction Museum that is in the lower 2 levels and enjoy all of the great exhibits from my favorite TV and movie memorabilia  There was even more SQLKaraoke with a live band there, guess that is the new trend for the Summit this year. I didn't make it to SQLKaraoke at Bush Gardens until Friday evening this year, just too much going on during the week.

And that is it for another Summit, it was a crazy week that went by far too fast. I had a great time meeting everyone for the first time and again and I'm already looking forward to 2013 in Charlotte!

PASS Summit 2012 - Part 1 (Pre-Summit Activities)

While I'm sitting in the Atlanta airport waiting for my connecting flight to Columbia, SC, thought that it was a great time to get some of my thoughts on last week's PASS Summit 2012 posted. This post will cover the activities I did before Summit.

I arrived in Seattle on the Friday morning to spend some time with some of the other Pragmatic Works folks in the Mount Rainer area. Being in Denver, I'm not in direct contact with most of the other Pragmatic Works people on a regular basis, so this was a great way to get to know more of them. Friday night we all ate at the cabin, thanks to Brian Knight our chef for the evening. The cabin was pretty cool, if not a bit old. Had a fireplace, pool table and Foosball table and tons of beds for all of us.

We all attempted to hike up Mt. Rainer first thing Saturday morning. Weather was not great as we left the cabin, raining, foggy and pretty windy. We got to the parking lot to get all of our gear setup and this was the first time I have ever hiked anything more than just some paved trails in Colorado, but I figured I live a mile high so I shouldn't have too many issues. Oh, was I completely wrong! Didn't take me very long to realize that even living at altitude doesn't help if you don't also work out regularly at altitude (those that have seen me recently know just how out of shape I am). Thankfully we were prepared for the full group of 9 people to split up into at least 3 smaller groups as the hike went on. I quickly feel back into the back group, which was great because we were all pushing each other on to continue even when at different times all of us wanted to just quit and go back. We got all of the way up to around 7000 feet and to the point where the snowfield starts and we would have to put on our snowshoes. That was when Mt. Rainer really let us know that it was in control and the wind was blowing at 30mph+ with sleet and snow blowing in our face. We stood there for awhile and realized that we could see the first of the groups coming back our way. Turned out that not too much farther ahead they hit snow that was waist deep and even harder going, so we all made the decision to head back to the cars. No one got to the goal of the basecamp (Camp Muir) this year. According to others that have done this hike at other times the snow was higher then they have ever seen it. It was very grueling for me, but I'm glad that I tried it and made it as far as I did. I don't think that I will ever need to try and hike a mountain again, but you never know.

After the Mt. Rainer adventure all of us headed back to Seattle on Sunday morning. Before leaving I made breakfast for everyone. Thankfully I didn't make anyone sick (or at least they didn't tell me) with my French Toast, Bacon and Hash Brown feast. On the way into Seattle we all stopped at the mall in Bellevue since some in the group wanted to stop at the Microsoft Store and see about getting a Surface. I had seen Brian Knight's Surface at the cabin previously, so I didn't see the need to get one (I'm already an Apple fanboy, I'm more interested in the Windows 8 Pro tablets). This was also my first time in a Microsoft Store, definitely looks like a copy of the Apple Stores, guess you need to copy something that works! :)  While I didn't get a Surface I did wander to the back of the store and found an awesome Ogio Bandit backpack that is White and Black with Gold highlights. As soon as I saw that bag I had to have it since those are my Alma mater's Purdue University colors (only thing missing is Purdue patch, which I'm going to fix). After a couple of people bought Surface's we headed off to P.F. Chang's for lunch. Was great to sit down and talk about the weekend and prepare ourselves for the week to come.

We headed out of Bellevue into Seattle and dropped everyone off a their hotels for the next week. I was staying at the Sheraton again this year. Definitely my favorite hotel for PASS Summit in Seattle since you only have to walk half a block to get into the convention center (wish it was cheaper though). The only thing that I found after checking-in that sucked is that our normal staging area for post-conference activities each night the Sheraton bar was closed for renovations. Now we would all have to use Twitter to find out what was going on or make sure to contact attendees at the conference/hotel as we saw each other.

On Monday I didn't have any PASS Summit related activities, so I got up early to make a visit to Top Pot Doughnuts. If you have never been to Top Pot, you should definitely try to go next time you are in Seattle, very good doughnuts! After that I walked down to the pier area to walk around and saw the new Seattle Giant Wheel that was under construction that last 2 trips I made to Seattle. Before lunch I meant up with a few other Pragmatic Works people to take the Seattle Underground tour. I have done this tour twice previously, but it is always great to go on this tour to get the different stories on Seattle's very colorful past. If you have not done the Seattle Underground tour, you should best tour I have done in any city I have visited. Grabbed some lunch after the tour and then headed back to the hotel to recharge before the Networking Event that evening.

That covers all of the pre-Summit activities, so I'll post the details on the rest of the week in a future post.

Midlands SQL PASS Chapter Meeting - 11/13/2012

Just a quick note that I will be going pretty much straight from PASS Summit 2012 in Seattle (stay tuned for detailed blog post on this) to Columbia, SC and presenting "SSIS 2012: More Than Just a Pretty UI" at the Midlands SQL PASS Chapter meeting on Tuesday, 11/13 at 5:30pm. If you are in the Columbia, SC area on Tuesday, 11/13, please stop by. Looking forward to presenting at another PASS SQL chapter next week!

For more details go to the Midlands SQL PASS Chapter website.

SQL Saturday #153 and PASS Summit 2012

For those in the Salt Lake City area this weekend, I will be speaking at the SQL Saturday event that will be held on Saturday, October 20 starting at 9am. I am currently scheduled to speak at 2:45pm in Room 206 on SSIS 2012. For those that are new to the SQL community, SQL Saturday's are free events held throughout the world where SQL Server Professionals share their experiences for a day of learning and networking. If you are in the SLC area this Saturday, you should come by! For more information and to register, please go to http://www.utahsqlsaturday.com. See you on Saturday!

I will also be attending PASS Summit 2012 in Seattle coming up on November 5-9. If have the means to go to PASS Summit, I would highly recommend it, it is the biggest SQL Server focused conference in the US and is a great place to meet SQL Server Professionals from all over the world. There are so many things to do and see both at the conference and after the conference. To get all of the details and register, go to http://www.sqlpass.org/summit. If you are from Colorado and traveling to PASS Summit 2012, we will be having our annual dinner at Elliot's Oyster House on Thursday, November 8th at 6:30pm. You will have to cover your own food and drink, but it is a great chance to meet with the SQL Professionals in the Colorado area outside of the conference. If you are interested, please register at http://cosummit2012dinner.eventbrite.com. Hope to see you there!

SQL Saturday #169 Denver Wrap-up

SQL Saturday #169 Denver was held last Saturday (9/22) at the Cherry Creek Presbyterian Church - Community Life Center, after months of planning and worrying about how the event would go, it turned out to be a very successful event! We met our registration cut-off amount of 300 late on Friday night and the final count at the end of day on Saturday was around 220 that actually attended! Really good since we did not hold a SQL Saturday in 2011 (the first for this area was in September 2010) and shows just how much the SQL Server community has grown.

We started the planning of this event in May/June, which is a bit late for an event of this size. We pulled together a great group of volunteers from the area to help us out and that made all the difference. It is great to see so many people that are willing to give up some of their free time to make sure that we had a successful event. Without them this event would not have been as good as it was, so thank you to everyone that volunteered to help!

The number of sponsors that we had was also a huge help to make the event such a success. Big kudos to Confio Software, they are a local Colorado company and they came through big as a Gold sponsor with lots of last minute support in various forms. I also want to send out a big thank you to Quanta Intelligence for sponsoring the Speaker/Volunteer dinner on Friday, everyone had a great time before all of the hard work on Saturday started. Another big thanks to Datavail who came in as a Gold level sponsor and sponsored the After Party on Saturday evening. Without these sponsors and all of the others - DawaBI, Pragmatic Works, Xtivia, PASS, Red Gate, Fusion IO, Quest Software, COZYROC, Qortex, SQL Cruise, Sapien Technlogies, McGraw Hill, Wrox and Pluralsight we could not had this event.

Since this was the first time that I was the lead coordinator on this kind of an event I decided early on that I would not present a session, since I knew that I would already have enough stress for the day. That was a good decision because there were many challenges that needed my attention throughout the day. We have a list of things that we know about now and will keep in mind for the setup of the next event, and for those wondering, yes I'm sure that we will be having another SQL Saturday in the near future. Overall it was a great learning experience I'm looking forward to the next one and being able to enjoy it more and to roam around and soak it all in more.

To get a different perspective on the event, check out the following blog posts that have been posted by others:

http://diditsave.wordpress.com/2012/09/25/sql-saturday-169-denver-recap/

http://sqlserverperformance.wordpress.com/2012/09/23/dmv-emergency-room/

http://extofer.com/blog/sql-saturday-169-in-the-bag 

http://www.zero1design.com/2012/09/23/what-i-learned-at-sql-saturday-169/

Thanks again to everyone that was involved in our SQL Saturday and a big thanks to everyone that attended the event!

SQL Server 2012 Database Projects

After I got really confused on why some things were possible in different combinations of SQL Server 2012 and Visual Studio 2010 for SQL Server 2012 Database Projects I thought that I would get what I found out for others too that might be as confused as I was.

For those that might not know Microsoft introduced Database Projects awhile back, but they had some issues where the scripts created didn't always work and the schema comparisons didn't work well either. Being a BI Developer at that time I didn't spend much time looking at it, just heard what the other DB Developers were complaining about. Database Projects were meant to be a way for DB Developers and DBAs to help get the database "source code" into version control and make it easier to create scripts for deployment into multiple environments.

So, with SQL Server 2012 there were some changes made to the Database Projects and from what I have seen using it lately they are some very good improvements. But, the side effect is that there are 2 tools with a very similar name that are slightly different. The 2 tools are SQL Server Data Tools and Microsoft SQL Server Data Tools (wow big difference huh, one with Microsoft in front one without).

SQL Server Data Tools (SSDT)

For those that have installed any version of Microsoft SQL Server 2012 you know that you can install SQL Server Data Tools as part of the database instance install. This is the tool that uses the Visual Studio 2010 Shell and is the replacement for the Business Intelligence Development Studio (BIDS) in previous versions of SQL Server. Since they wanted to add more functionality then just BI development projects the tool needed to be renamed. SSDT still works with all of the BI projects as before.

Microsoft SQL Server Data Tools

This is the web install version that is available for anyone to download and install without any requirements for SQL Server 2012. As with SSDT this is built on the Visual Studio 2010 Shell. The difference with this version is that it only does the Database Project development (including SQL Azure). If you install only this version of SSDT you will NOT be able to do any of BI development unless you also have the SSDT from SQL Server 2012 installed.

Not sure why Microsoft went down this route and I find this very confusing and it gets even worse based on the version of Visual Studio 2010 that you have installed. If you want to do both BI Projects and Database Projects you will need to install the SSDT from the SQL Server 2012 install media AND the Microsoft SQL Server Data Tools from the web link above. But, even with this there are some differences in the projects! The pieces that I found which are missing are only in the Database Projects and are specific to building these projects using the new SQL Server Database Projects.

Below is what the SQL Server Database Project structure looks like when you create it using the web download of SSDT and no version of Visual Studio 2010 installed:

The screenshot below is against the exact same SQL 2012 database, the only difference is that I created this first as a new SQL Server Data-tier Application Project and then converted it to a SQL Server Database Project (you do this by right-clicking on project file in the Solution Explorer and selecting Convert to SQL Server Database Project...) on a machine that had Visual Studio 2010 Ultimate (Premium also works from what I found researching this) and the SSDT web install:

Notice the differences? There are some folders and scripts that are not being created by the "lite" version, like the Scripts folder with the Pre-Deployment/Post-Deployment files along with others. Based on what I have started to script I find the Pre/Post Deployment scripts to be very useful since they are pulled in automatically when you create the deployment scripts. I use the Post-Deployment script to setup SQL Agent jobs or anything else that can be done after the database has been created.

You maybe asking, can I just create that folder structure manually in the "lite" project and have it automatically pull that in? Microsoft has actually included all of the pre/post deployment scripting capabilities in the new SQL Server Data Projects and you can create them anywhere in your project by adding a new script item to your project and selecting the Pre-Deployment Script or Post-Deployment Script types that appear in the Add New Item dialog. While I don't understand why the initial creation of the projects has changed so that these folders/scripts are not automatically included at least they are both capable of doing it you just have to know how to add it.

I have verified that if you create the SQL Server Database Project as a converted SQL Server Data-tier Application Project in Visual Studio Ultimate/Premium and then open it in the "lite" version it does still see all the extra folders and it will script them correctly.

So this is what I have found after playing around for awhile and just about pulling out my hair when I couldn't figure out why one worked differently then the other. I believe that most of these differences comes from how the SQL Server Data-tier Application Projects (DAC) were setup and those differences in that project type are being carried over when it is converted to a SQL Server Database Project. I still find it very confusing that creating the project in one way and converting to the "new" way causes so many of these differences. Microsoft could have addressed this by making sure that the new SQL Server Database Projects for SQL Server 2012 have the exact same structure as the previous version, but it seems that they see each of these "new" project types as a chance to start over and do it differently.

SQL Saturday #169 - One Week Left!

Only one week left to submit your sessions for SQL Saturday #169 in Denver on September 22, 2012! If you have a topic to share and you are going to be in the Denver area on September 22nd, please submit your session for this great event by 7/31/2012!

We are still looking for sponsors as well, so please take a look at the sponsorship options that we have available and register here.

If you are going to attend the event, we ask that you register so that we know how many to expect.

Looking forward to putting on a great event in a couple of months!

SQL Saturday

Wanted to put out a quick post that I have not had the time to put up until now. If you are going to be in the Denver, CO area around September 22, 2012 I would encourage you to register for a day of SQL Server technical training given by many local and national speakers. Yes, that's right we are doing SQL Saturday #169 at the same location as 2010, Cherry Creek Presbyterian Church (10150 E Belleview Ave., Greenwood Village, CO 80111).


We are asking that anyone that registers pay a $10 fee for lunch, this will help us to provide all attendees with a lunch that is not pizza and provide plenty of beverages throughout the event. Please register to attend and we look forward to seeing you there!


If you are a speaker, please submit your session abstracts on our Call for Speakers page. And if you are sponsor, please go to our Sponsors page to help us out!


I would also like to mention that Brian Knight, Founder of Pragmatic Works will be presenting a 2 day SSAS workshop on September 20-21 at the Denver Microsoft office (7595 Technology Way, Suite 400, Denver, CO 80237). If you are interested in attending that event, you can go to the Pragmatic Works registration page linked here. You will need to bring your own computer to that workshop with the SQL bits already installed along with AdventureWorks installed, more details can be found on the link above.

Also, in other SQL Saturday news I will be presenting at SQL Saturday #159 in Kansas City, MO on August 4, 2012. Looking forward to getting some great BBQ in Kansas City and presenting my "SSIS 2012: More Than Just a Pretty UI" session, so come see me if you are going to be at SQL Saturday #159!

I will post more on my presentation at SQL Saturday #159 and our own SQL Saturday #169 over the next few months!