1. The ability to create and compare snapshots of objects
2. Impact analysis across SQL Server, SSIS, SSAS, and SSRS
3. Data profiling within the created documentation

Some of these features are discussed in this short video:

In addition to the three big ticket items listed above, there also seemed to be some rather major improvements to the user interface, an addition of SSIS package pictures to the documentation created, and  the ability to create and document database diagrams within the tool set.  Quite a list of new features!

Bottom-line, this looks like a solid tool that will really be helpful to better leverage all of the metadata associated with SQL Server, SSIS, SSAS, and SSRS.  Taking this a step further and adding SharePoint/Performance Point integration as well will really round out the offerings and truly provide one place to gather all pertinent business intelligence and data lineage information.

The Data Viewer functionality within SSIS is a very useful feature which can be used to solve data related problems within a package.  With that, the main use and most common application of this functionality is troubleshooting data issues during the SSIS development lifecycle.

In an effort to allow developers the ability to see data as it flows through the SSIS pipeline, SSIS utilizes the Data Viewer.  The Data Viewer can be added to any path within the package data flow and it can display the data output in a variety of formats.

Setting up the Data Viewer is a Simple SSIS task – simply right click on the data path (success or failure) and select “Data Viewer” from the menu.  You can then easily add a Data Viewer to the stream, run the package, and see the data output. 

Setting Up the Grid Data Viewer and Results

The following video shows this process and the results using the Grid Data Viewer.  The grid is the most commonly used of the four types of Data Viewers and allows the developers to see raw data in columns and rows.

httpv://www.youtube.com/watch?v=PCGWU_UBeMg

It is important to note that when the package is run with a data viewer in place, you can either allow the data to continue down the path to completion by clicking the “Detach” button on the data viewer window.

Showing the Histogram Data Viewer Results

Along with the Grid Data Viewer, developers can also make use of the Histogram Data Viewer.  This viewer is used for numeric columns and can help give insight as to how data is distributed across a range.  The Histogram Data Viewer can be seen in action in the following video.

httpv://www.youtube.com/watch?v=pKCZ-DYbkVU

Showing the Scatter Plot Data Viewer Results

Another Data Viewer that can be used with numeric data – actually two numeric columns are needed – is the Scatter Plot Data Viewer.  This viewer can help uncover relationships between the two selected numeric columns.  Once again, the following video will show the configuration and results of the Scatter Plot Data Viewer.

httpv://www.youtube.com/watch?v=275_zW4pPy4

Showing the Column Chart Data Viewer Results

Unlike the Histogram and Scatter Plot Data Viewers, the Column Chart Data Viewer can be used with numeric or non-numeric data.  The purpose of this viewer is to show a summary of the values within a particular column in the data flow.  As in prior sections, this viewer is shown in action in the following video.

httpv://www.youtube.com/watch?v=lLN5opE7GlQ

While the video isn’t all inclusive and left me with a lot of questions, I think it did a good job of illustrating the basics – not just in academic terms, but in a real life scenario that I was able to apply to my current situation.

Definitely worth the 5 minutes…

httpv://www.youtube.com/watch?v=nUsDZ4CBu6I

For a little background, there are basically three (but really only two) main options for deploying SSIS packages:

1. File System
2. SQL Server
3. SSIS Package Store (really just a version of #1)

Each of these options has certain advantages and disadvantages and none of them are the clear cut winner. Go figure.

Deploying to the File System

The key advantage of this solution is that it is drop dead simple.  All you need to do is take the dtsx files you’ve created in BIDS locally and move them to a directory in the SSIS Server of your choice.  You can then use the DTEXEC executable on the server to run the dtsx files from there.  Additionally, you can schedule them to run via the SQL Agent and set any necessary parameters or command line setting necessary for the file to run correctly.  This is nice because there is no need for complicated configurations or added security, just put the files out there and let them run. 

Another benefit to the file system deployment is backup and recovery.  As long as the file system that holds your packages is properly backed up, recovery from a major melt-down is simple and straight forward.  An additional benefit is the ability to recover single files from the backup rather than the entire directory.

Easy, right?  Yeah.  I agree, it seems like a no-brainer, but – like anything -  there are a few downsides to consider as well.

For starters, any security you would like in place needs to be handled at the file/folder level using Active Directory to assign the access to the directory folders and associated files.  While this isn’t a huge issue nor necessarily bad, I think the SQL Server option provides a better implementation model, increases flexibility, and improves security.  Along with security, there is limited audit ability included in utilizing a file systems deployment.  While it may be possible to track who put a new file in place or overwrote an existing file with a newer version, it is not ideal.   

Deploying to SQL Server

This is definitely the most complicated deployment option, but it does offer the best security options and execution performance that is on par with the other options.  There are also SSIS specific SQL Server roles that can be granted to allow (or not allow) people to see, execute, and interact with SSIS packages stored in the database.  Additionally, the packages can be encrypted within the database which allows for yet another layer of package security.  Not bad.

Backing up SSIS packages stored in the database can be accomplished by backing up the database.  While this is simple and most DBA’s will already have this in place, it does come with its drawbacks.  The primary issue with this is that it is not possible to restore or recover a single SSIS package by using the database backup – the only option would be to restore ALL of the SSIS packages stored in the data base.  This can be rather easily overcome by utilizing a source control product like Microsoft Visual Source Safe to store current version of all packages stored in the database.

Deploying to SQL Server seems to be the best option for our environment.  While it doesn’t solve every problem – and actually creates a few of its own – it seems to me to be the most easily manageable deployment method of the options given.

Deploying to the SSIS Package Store

As the SSIS Package store is really just file location within the SQL Server installation, using the SISS Package Store for deployment has all of the same challenges and advantages as utilizing the file system.  The location of the SSIS Package Store is Program Files\Microsoft SQL Server\90\DTS\Packages directory. 

Resources

I gathered the above information from a variety of resources, most all of which are listed below.

• Pragmatic Works FAQ
• SSIS Junkie Blog at EMC Consulting
• Managing and Deploying SSIS on MSDN
• SQLCAT Top 10 SSIS Best Practices
• Microsoft TechNet: Using SSIS Roles in SQL Server

Additionally, I found the following table from Pragmatic Works insightful at a glance and useful to get a basic idea of the important issues and how the main deployment options perform in regards to those issues.

httpv://www.youtube.com/watch?v=TPyCeRbp9k0

The Turning Point

Recently I read a great blog post by Brent Ozar titled, “Rock Stars, Normal People, and You” which really hit home with me. For those of you who don’t know (if there are any readers left out there), Brent Ozar is a Microsoft SQL Server MCM and MVP, DBA, author, and seemly (only because I haven’t met him) all around great guy. The post was a rewind of where Brent had been, what was wrong with where he was, and what he did to make things better and improve his professional life. A considerable part of the post revolves around his career path, various missteps, and what eventually led him to a successful fit with a company and career, but the undertone of the post – and the part that had the most impact on me – revolved more around his personal accountability for his career. In a nutshell, his theme was if you want a successful, fulfilling career you have to go get it. You have to make it happen. It’s not going to fall in to your lap. No one is going to hand you the perfect job. Most importantly, you need to start now – right now – find something you are passionate about, work hard at it, and success will come your way. The Rock Stars of today were once the Normal People sitting in the next cube over.

I’m not entirely sure why this particular post had such an impact on me. Most likely because it was saying loud and clear what I have been avoiding to say for some time – I am solely responsible for my career and only I can make it either wildly successful or a dismal failure. I can be happy, passionate, and driven about what I do or I can be a mediocre clock puncher, looking forward to the end of the day.

I’m Going to be a Rock Star (Some Day)

The good news is that I am personally at a great point in my career and I am looking to move things to the next level. While Brent’s post spoke to me, it has really been over the past couple of months that I came to the realization that the career path I was headed down was not for me, not where my passion was, and not what I saw myself doing long term. You see, I started my career on the tech side of IT – doing the work, learning the technology, interacting with my peers – and I loved it. Loved it so much in fact that I thought the best thing for me was to step in to management and help run the teams that build stuff. Seems like the next step, right? And, in fact, this was good for a while and I did well. I was successful. I started a brand new team, positively impacted IT processes, and generally made things some better. However, while I was continuing to be successful as a supervisor – I was failing to figure out how to take things to the next level, how to go from doing a good job and getting things done to being instrumental and a true leader within the organization.

So What to Do?

After some personal reflection and a few very candid, yet positive, discussions with my manager I came to the realization that my passion is not in managing – my interests and passions lie in doing and building and finding creative solutions to tough technical problems. All the stuff I have been tasking my team to do is really the stuff I want to do myself. Coincidently, the team I have been managing has been looking for a senior level developer. With my manager’s approval, as of April 12th I am no longer the Supervisor of the Business Intelligence team; I am now a Sr. Systems Developer in Business Intelligence. Finally, I work for a company I believe in (www.capella.edu), I have manager that seems to have faith in me and my skills, and I really believe that I have a lot to offer – both to my company and the SQL Server community as a whole. I have a wealth of knowledge and experience in business intelligence and I have a hunger to learn more and become better at what I do. Oh yeah, and I know how lucky I am.

Next Steps

I feel like I have a world of possibilities ahead of me and that’s great, but I also realize that I need to focus and have a plan. I also know enough to know that I have a lot to learn. With that, I have a few things in mind…

• Network – leverage the great local SQL Server community to help expand my knowledge and have resources to call on when I need help.
• Learn – read, read, and read more.
• Challenge Myself – I need to take on the tough tasks, work through them, and grow from the challenge
• Write – pass along the things I learn to others. I need to remember that even though I don’t know everything, there are others out there that know even less than me and may benefit from what I have to say.

I know this isn’t really a detailed plan, but it’s a start. I’ll add more detail as I figure it out…

Another great workout today. 6 miles on the treadmill – tempo run. 2 miles easy (9:23 pace), 3 miles short tempo (7:47 pace), and 1 mile easy (9:2 pace) for a total of 6 miles. (link) – September 28, 2007

Being that yesterday was a rest day, today kicked off the actual workouts for my triathlon aspirations. My first workout was a swim workout and, while I have been swimming on and off for a while, it was a challenging and satisfying workout. Here are the details… link – December 10, 2008

I’m using this post to simply test windows live writer and see if I like using this to write and publish my blog content. So far, this is a great little tool.
I’ll keep working with it to see if it is going to be worthwhile. link – August 22, 2007

And on and on it goes… Aaron worked out… Aaron ran… Aaron Swam… Not very enlightening. Not even a very honest or open look in to my life. With that, there are a few posts that are very meaningful to me and important to the story of my life, like this one:

…the doctors were ready to fill me in on what was wrong with me, why I was feeling so lousy and what we needed to do to make things better. What they found was not good. When the Surgeon did my Gallbladder surgery he must have accidentally nicked my Abdominal Aortic Artery, so for the last week I had been bleeding into my belly. They called my condition a “Psudeo-Anurism”, meaning I was bleeding into my belly, but it was in a confined area; this is what was keeping me from bleeding to death. They rushed me (by Ambulance, code 3) to St. Joe’s hospital in St. Paul and I was in surgery less than an hour later. link – March 8, 2006

This was an important time in my life and I am so glad that I have it documented here and I can look back to this point in my life as a reminder of where I have been, what I have gone through, and why it is so important that I keep moving forward in a positive way. Everything can be taken away. This is a lesson that took me a long time to learn, but one that I’ll never forget…

So, on to the new…

As I said, when I look at the posts I have made (almost 300 at this point!) I think they reflect one part of my life, but maybe not the part that should be reflected. While I guess it’s ok that this blog shows how far I’ve run (or haven’t run), or how many hours I’ve logged in the pool, or how much I weigh – I think I can do more. I mean really, no one cares about this crap and – most importantly – in 10 years I won’t care about it either. If I’m going to take the time to write, shouldn’t it be meaningful?

So, with that, I am making a commitment to improve the quality, depth, and content of my blog posts. I’m going to write about things that are important to me – not what I think might be important to others…

Aaron

WARM UP:
200 Swim – 200 Kick – 200 Pull

MAIN SET:
4 x 300 @ below 70% with :30 rest

Concentrate on being relaxed in the water and getting the “feel” of your stroke. Speed is not important here. Your stroke count should be even every lap of every repeat.

COOL DOWN:
200 easy swim, continuous

Overall, not a bad workout – 2000 yards. It said it was supposed to take 45 minutes, it actually took a little less than that.

Tomorrow brings and hour and 15 minutes on the bike. We’ll see how that goes…

Bye for now…

Aaron

Today started the first week of a 39 week triathlon training program that I’m using to get in shape for next spring/summer and the triathlon season. Hopefully if everything goes as planned, I’ll update my training here, once a week.

If you are interested, you can look at the training plan here. Monday’s (like today) are a day off – great way to start! Tomorrow is a swim day.

In the end, there are a few little nagging details I need to work out before I have an actual race. For example, I don’t have a bike suitable for a triathlon. I have a bike, actually a really good bike, but it’s one of those “comfort” style bikes. Great for hauling the kids around in the bike trailer and pedaling around the local parks, but I think the 2 inch wide tires and 30 pounds of bike would slow me down a bit in a triathlon. So, with that, if you have – or know anyone that has – a good road bike they’d like to lend me for next spring and summer let me know!

Another detail I need to figure out is what races I’m going to do. I’d like to do the North Mankato triathlon in June, but other than that I’m pretty open to anything within a couple hours drive of the Twin Cities. Any advice is welcome – if you’ve raced somewhere and had a good (or bad) experience, let me know!

I’m totally new to the whole triathlon experience and I’m looking forward to everything it has to offer. From what I can tell so far, this training program will get me in great shape and the constant change of pace will be nice. After only doing running for the last 3 years, getting something different in the mix will be fun!

I’m sure I’ll have more questions and comments as this moves forward. I’ll post them here and I look forward to sharing them with all of you!

Keep running, swimming, and biking!

Aaron