Brian Knight

SQL Server 2008 Business Intelligence Certification Test Beta Review

2008-07-03T18:07:44Z

Today, I took the beta version of the SQL Server 2008 BI certification test, which is free until 7/31. It was pretty grueling. I don't do well with standardized testing but this one was especially lengthy at 70 questions and up to 3 hours to take. It took me about an hour and a half but it was needless to say very disappointing when at the end of the test and the comment period I received this message where I should have had a score:

"Thank you for completing the beta version of the exam 071-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. You should receive your score report by mail before the live version of this exam is released. Score reports are usually sent eight weeks after the beta period ends. However, the processing of some beta exams might require additional time."

Then it was over. I have to wait at least 3 months from today to see if I passed or not. Well on to the review portion of the test.

The test was nearly equally weighted with SSIS, SSRS and SSAS. My cornerstone is SSIS, but I do have experience with the other platforms. I felt the SSIS portions were not terribly representative of the product. For example there was much talk of custom tasks and obscure settings, which I wouldn't have known unless I had just written a chapter on them a few months ago. The SSRS portion of the test focused heavily on administration, which was unusual I thought but I imagine it's quite hard to script a development question in SSRS. On the SSAS portion, there lots there on administration and designing of the cube and attribute hierarchies. I felt the SSAS exam best represented what a SSAS developer would be doing day to day so kudos go out to those writers.

I of course can't mention individual questions and answers but if you don't know the administration side of the three BI products, you will not likely pass since it was easily half to 60% administration. I assume the nature of beta exams is you can expect a much lengthier exam with non-representative questions. The nice thing, is you can comment on how you feel about each question after the test and I may've been too vocal :). As with any beta test, you can expect some questions to be just plain unfair or wrong. There was one SSIS question for example that had no right answer if you were looking with a picky eye. There was a right answer though when you looked through the goggles of the test writer.

Good luck and try it yourself this month to get in for free!

Brian Knight

Making the SSIS Data Reader Source Query Dynamic

2008-07-03T03:21:47Z

I was recently working with FoxPro for Unix as a data source using ODBC in SSIS (story for another day) and found some interesting scenarios inside of it. We wanted to pull the table name dynamically, thus changing the SELECT statement inside the SSIS Data Reader Source. Whilst this is quite easy in OLE DB Sources, there's no obvious way to do this in the Data Reader Source for ODBC or legacy connections as you can see in the below screenshot.

There's still a workaround, which feels like a hack but it works great. Before you start, change the name of the Data Reader Source to something that you're fine leaving it as because you're about to create a dependency on this name. To make the query dynamic from an SSIS Data Reader Source or ADO.net Source, select the Data Flow Task and in the Properties window, you'll see the name of the Data Reader Source with your query inside of it as shown in the below screenshot. This shows you which property you want to make dynamic, which in my case is the [DataReader Source].[SQLCommand] property. You then want to set the expression on that property by clicking the ellipsis button next to the Expression property. Lastly, create an expression like this, which makes the query dynamic using a variable:

"SELECT * FROM " @[User::TableName]

You may find yourself needing to turn off the ValidateExternalMetadata property if don't want your query to parse until execution instead of during the validation stage. You can set many properties this way in the data flow to be dynamic but it does require the names of the components you're setting to be static.

Brian Knight

Free SQL Server 2008 BI / Admin Cert Test (beta)

2008-07-02T03:14:38Z

If you're interested in becoming certified in SQL Server and are really, really driven, there's a free test for BI and SQL Server for this month only. You have to register soon to get one of the available open beta slots though. If you do pass, you'll be credit with the test once it's out of beta. If you fail, you're not out any money at all if you choose the promo code. I'm taking the BI one Thursday the 3rd and will post how it goes. The admin one I'm taking the following week.

See more information about how you can claim one of the slots here: http://blogs.msdn.com/gerryo/archive/2008/07/01/sql-server-beta-exams-extended-open-invitation.aspx

For the test use the promotion code of: 943F6 and there are lots of restrictions.

Webinar Summer Series Extended - 5 Days, 11 Hours of Free BI Training

2008-07-02T03:07:36Z

Due to popular demand, many of the webinars have reached the capacity of more than 1500 registered guests. To make sure everyone could participate, we have added an additional hour each day for those who missed the first session. You must register in order to attend and the second session is expected to reach capacity as well!

More information can be seen here: http://www.dtsxchange.com/freebisessions.asp

I've never done a webinar before with anywhere near this number so I'm curious how the technology scales :). It should be a great case study to put 1000 geeks on a concall and screen capturing system at once. If you can make it, we've added the extra slot and session for you but please register soon to get in.

Monday, July 14 @ 11:30 AM EDT 1hour
Upgrading DTS Packages to SSIS
The session will include an actual DTS package conversion using DTS xChange and other strategies. DTS xChange offers the latest automated DTS to SSIS migration technology used to complete SQL Server 2005 upgrades.
Sorry, session is full. Please attend afternoon session

Monday, July 14 @ 1:30 PM EDT 1hour
Upgrading DTS Packages to SSIS
The session will include an actual DTS package conversion using DTS xChange and other strategies. DTS xChange offers the latest automated DTS to SSIS migration technology used to complete SQL Server 2005 upgrades.
https://www2.gotomeeting.com/register/976091722

Tuesday, July 15 @ 11:30 AM EDT 1hour
Introduction to SQL Server Integration Services (SSIS)
This session will get you past the learning curve of SSIS. Brian covers creating an end to end package that covers many of the concepts in SSIS. He covers, looping over a set of files, processing each file and then archiving. You won't want to miss this if you're new to SSIS.
Sorry, session is full. Please attend afternoon session

Tuesday, July 15 @ 1:30 PM EDT 1hour
Introduction to SQL Server Integration Services (SSIS)
This session will get you past the learning curve of SSIS. Brian covers creating an end to end package that covers many of the concepts in SSIS. He covers, looping over a set of files, processing each file and then archiving. You won't want to miss this if you're new to SSIS.
https://www2.gotomeeting.com/register/471645031

Wednesday, July 16 @11:30 AM EDT 1hour
SQL Server Integration Services (SSIS) for the DBA
Brian will start by showing you the basics of SSIS then quickly jump into common DBA tasks like trying to integrate SSIS with dynamic management views (DMVs) to determine the worst performing queries across multiple databases and servers. If you're a DBA and have SSIS in your environment, you won't want to miss this presentation.
Sorry, session is full. Please attend afternoon session

Wednesday, July 16 @ 1:30 PM EDT 1hour
SQL Server Integration Services (SSIS) for the DBA
Brian will start by showing you the basics of SSIS then quickly jump into common DBA tasks like trying to integrate SSIS with dynamic management views (DMVs) to determine the worst performing queries across multiple databases and servers. If you're a DBA and have SSIS in your environment, you won't want to miss this presentation.
https://www2.gotomeeting.com/register/301757498

Thursday, July 17 @ 11:30 AM EDT 1hour
Performance Tuning SQL Server Integration Services (SSIS)
In this demo-rich presentation, Brian shows you some of the common and not so common ways to tune SQL Server Integration Services (SSIS). Learn how to tune the data flow using some of the advanced SSIS options and how to avoid common SSIS mistakes. See how to measure performance and how to keep SSIS from monopolizing your server's resourcing. Lastly, discover SQL Server 2008 features that will make SSIS more efficient.
Sorry, session is full. Please attend afternoon session

Thursday, July 17 @ 1:30 PM EDT 1hour
Performance Tuning SQL Server Integration Services (SSIS)
In this demo-rich presentation, Brian shows you some of the common and not so common ways to tune SQL Server Integration Services (SSIS). Learn how to tune the data flow using some of the advanced SSIS options and how to avoid common SSIS mistakes. See how to measure performance and how to keep SSIS from monopolizing your server's resourcing. Lastly, discover SQL Server 2008 features that will make SSIS more efficient.
https://www2.gotomeeting.com/register/220829813

Friday, July 17 @ 11:30 AM EDT 1hour
Administrating SQL Server Integration Services (SSIS)
In this session for DBAs, Brian Knight shows you how to configure your SSIS packages for a zero-touch deployment. Then Brian covers how to deploy, schedule and administer SSIS in production.
Sorry, session is full. Please attend afternoon session

Thursday, July 17 @ 4:00 PM EDT 1hour
Administrating SQL Server Integration Services (SSIS)
In this session for DBAs, Brian Knight shows you how to configure your SSIS packages for a zero-touch deployment. Then Brian covers how to deploy, schedule and administer SSIS in production.
https://www2.gotomeeting.com/register/296733568

Friday, July 18 @ 3:00 PM EDT 1hour
What's New In SQL Server 2008 Business Intelligence
This session will get you past the learning curve of SQL Server 2008. Brian will cover each of the BI platforms' best new features: SSIS, SSRS, and SSAS. Additionally, he'll cover many of the T-SQL new features that will effect business intelligence.
https://www2.gotomeeting.com/register/742337468

Follow Brian on Twitter

2008-06-26T14:15:08Z

I just signed up for a service called Twitter, where you can follow your co-workers through mini-micro posts throughout the day. If you interested in following the truly boring life of Brian, you can go to http://www.twitter.com/brianknight.

Another SSIS Webinar: Administering, Deploying and Configuring SQL Server Integration Services (SSIS) Packages

2008-06-26T13:12:57Z

In this session by SQL Server MVP, Brian Knight, you’ll learn how to make a package that needs zero configuration as you migrate from development to production. He will then show you how to deploy your packages and administer the SSIS service. You’ll also learn how to:

-Create configuration entries for dynamic configuration of packages
-Deploying SSIS packages to various environments
-Administering the SSIS service
-Scheduling packages

Register for this free session at: https://www2.gotomeeting.com/register/430701585

5 Hours of Free Training - The BI Series

2008-06-25T12:08:54Z

Nothing gives me more pleasure than teaching and getting the "aha" moment out of a student when the student gets over the learning curve of a potentially difficult product. That feeling on a larger scale is what is exciting me most to announce a free business intelligence webinar series during the week of July 14th to get you ramped up to SQL Server business intelligence and especially SSIS. Each day for an hour, I'm going to provide you with free training on exactly what you need to know to do your job as a BI Developer or DBA. Below, you'll see a list of upcoming webinars that are very demo intensive to get you past that slight learning curve and after the week, you'll be able to create SSIS packages that are applicable to your job. More may be added but I wanted to guarantee one a day and measure the response.

Registration is on a per-day basis to only the sessions you want to attend so if you'd like to register for more than one days, please click each of the links for the sessions you want to go to below.

Registration is open to the first 1000 people and due to a number of places promoting it, we do expect to hit that number so please reserve your virtual seat fast!

Upgrading DTS Packages to SSIS

Mon, Jul 14, 2008 11:30 AM - 12:30 PM EDT

Join SQL Server MVP and SSIS author Brian Knight to discuss the strategies of upgrading DTS packages to SSS. The session will include an actual DTS package conversion using DTS xChange and other strategies. DTS xChange offers the latest automated DTS to SSIS migration technology used to complete SQL Server 2005 upgrades. Attendees to this session will all receive a copy of DTS Profiler (a $495 value), which will help you scope your DTS migration project.

https://www2.gotomeeting.com/register/520992041

Introduction to SQL Server Integration Services (SSIS)

Tue, Jul 15, 2008 11:30 AM - 12:30 PM EDT

Join SQL server MVP and SSIS author Brian Knight in this webinar that will get you past the learning curve of SSIS. Brian covers creating an end to end package that covers many of the concepts in SSIS. He covers, looping over a set of files, processing each file and then archiving. You won't want to miss this if you're new to SSIS.

https://www2.gotomeeting.com/register/359086032

SQL Server Integration Services (SSIS) for the DBA

Wed, Jul 16, 2008 11:30 AM - 12:30 PM EDT

Join SQL Server MVP and author, Brian Knight to see how you can use SSIS to complete and automate a number of common DBA tasks. Brian will start by showing you the basics of SSIS then quickly jump into common DBA tasks like trying to integrate SSIS with dynamic management views (DMVs) to determine the worst performing queries across multiple databases and servers. If you're a DBA and have SSIS in your environment, you won't want to miss this presentation.

https://www2.gotomeeting.com/register/328130178

Performance Tuning SQL Server Integration Services (SSIS)

Thursday, July 17, 2008 11:30 AM - 12:30 PM EDT

Join SQL Server MVP and author, Brian Knight to see how you can get the most out of SSIS. In this demo-rich presentation, Brian shows you some of the common and not so common ways to tune SQL Server Integration Services (SSIS). Learn how to tune the data flow using some of the advanced SSIS options and how to avoid common SSIS mistakes. See how to measure performance and how to keep SSIS from monopolizing your server's resourcing. Lastly, discover SQL Server 2008 features that will make SSIS more efficient.

https://www2.gotomeeting.com/register/248774021

What's New In SQL Server 2008 Business Intelligence

Friday, July 18, 2008 3:00 PM - 4:00 PM EDT (note the different time!)

Join SQL server MVP and SSIS author Brian Knight in this webinar that will get you past the learning curve of SQL Server 2008. He'll cover each fo the BI platforms' best new features: SSIS, SSRS, and SSAS. Additionally, he'll cover many of the T-SQL new features that will effect business intelligence.

https://www2.gotomeeting.com/register/742337468

Speaking at JSSUG Meeting

2008-06-23T02:22:27Z

JSSUG will be meeting next week on Wednesday the 25th at 6PM at the BOA campus.

Where: "Driftwood" room (2nd floor) in Building 500, Bank of America, 9000 Southside Blvd: Map and Directions

Topic: New Features in SQL Server 2008 - In this session, Brian will show the best SQL Server 2008 new features for DBAs, developers and BI engineers. He will cover some of the new T-SQL enhancements like Merge, CDC and others, the performance warehouse, Policy framework and the new Reporting Services.

1 Lucky winner will win: TechNet Direct Subscription. TechNet Plus Direct offers full version non time bombed Microsoft software. Windows Vista, Office 2007, Exchange 2007,SQL, ISA, Windows Server, and so much more. Subscribers can download ISO images of the software, burn them to DVD?s and then install them.

As always JSSUG meetings are free but we ask that you please RSVP so we can order enough pizza. Please RSVP to bknight<AT>pragmaticworks.com

Bootstrapping a Business

2008-06-23T02:13:30Z

All of my ideas seem to come to me on drives to clients or work. About 3 years ago, I was driving to work when I had the idea for JumpstartTV.com. The site was going to be a central how-to hub for videos on the web. The old saying is once you come up with an idea, you have to implement it in 18 months or you'll lose it to someone else. With bandwidth becoming so cheap, 18 months was unrealistic. In this world of the Web 2.0, in hindsight, we had to react in 6-8 months. So, I formed a team of trusted developers and we set out to make a website and a business.

Our first decision after we formed a rudimentary business plan was whether to fund the website ourselves or through an option like venture capital. Having funded my first few companies like SQLServerCentral.com successfully with a $50 investment from the partners, I decided to lean towards bootstrapping the business. In other words, spend my weekends and nights growing and building the business with minimal investment by pulling up my bootstraps and loosing sleep. We leaned towards this strategy because we didn't want to give up more than 50% of what we thought was a fantastic idea. The strategy worked well during the development cycle, allowing us to release the website in 4 months.

While we worked on our version 2.0 of JumpstartTV, other websites began to launch with similar concepts and on an accelerating scale. These other websites had a massive amount of funding behind them. Two such websites had received 35 and 40 million dollars of investment capital to scale their efforts. We began to fight to keep up with them on the amount of content we could produce "on the side" bootstrapping.

Knowing we couldn't keep up, we struggled. At a certain point, it was difficult to justify putting more money into generating content but then at the same time, I received letter after letter of gratitude for the site, justifying the site's purpose. Ultimately, there wasn't enough people pulling the cart and we stalled.

What I learned from this entire experience I thought I'd share in this blog post. My first lesson learned was to really know your competition. If you have a business where people are probably going to be pouring money into, you've better be willing to match that momentum. Bootstrapping works great but in some cases, if your competitor has $40 million, it will not work unless you're particularly blessed. If you can't match them in funds, try a different route. Find a more unique idea or become more focused. In my case, I had a broad website that had thousands of how-to videos about everything from how to make a drink to how to restore a database. If I had focused on a given market first and really conquered it first, you'd be reading a different post here. In other industries this may mean to not start just another dating site. Instead focus on baby boomer or divorced parents dating.

In case you were wondering also what happened to JumpstartTV.com, well it is about to relaunch in a big way after I merged it into another company with new partners. These partners (Steve Jones and Andy Warren) you may recognize since they helped me put SQLServerCentral.com on the map. Because of this, we now have a full time staff working on the site's relaunch, scheduling this week. It will be more focused on technology and will have a new level of content that I could never have done bootstrapping :).

SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

2008-06-05T00:05:03Z

I had been working in SQL Server 2008 for a number of months while working on the upcoming 2008 books and never had experienced this issue until just today. In hindsight, I can't imagine how I didn't experience sooner. The behavior change is when you save a table in Management Studio that requires the table be dropped and recreated behind the scenes, the change will fail by default with the below warning. In SQL Server, tables are dropped, recreated and reloaded automatically for you without having to worry about what's going on behind the curtains but this may create issues for some users. This behavior is required upon a number of actions but most common is creating a new column in a specific location in the table.

Warning Message:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

-- Brian Knight

Using RegEx in SSIS to Remove Given Characters

2008-06-03T13:29:16Z

In SSIS, we recently were engaged by a customer who had a fairly simple request. They wished to remove non-numeric items from a column in the data flow. They were doing this in a stored procedure by using a T-SQL replace function row-by-row and we wanted to make this a data flow instead due to speed. While we could have done this in a series of Derived Column transforms, we wanted to disallow any non-numeric field and hard coding a list of disallowed characters would have been quite taxing.

To do this in RegEx in the data flow, you can download the RegEx transform here: http://www.microsoft.com/downloads/details.aspx?FamilyID=c16f11ad-150a-4091-b3a2-83d21d3e0973&displaylang=en or use a Script Transform (Script Component). First drag the Script Component over and select Transform as the role you wish it to play. Next, select the columns that you wish to pass into the script and select ReadWrite. In the Script Tab, click Design Script.

In the Imports area, add the following line of code:

Imports System.Text.RegularExpressions

Next, replace the Input0_ProcessInputRow subroutine with the following code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.ProductNumber_IsNull = False Or Row.ProductNumber = "" Then

Dim pattern As String = String.Empty

Dim r As Regex = Nothing

pattern = "[^0-9]"
r = New Regex(pattern, RegexOptions.Compiled)

Row.ProductNumber = Regex.Replace(Row.ProductNumber, pattern, "")
End If
End Sub

You'll see in the above that the pattern is currently set to [^0-9]. The ^ symbol means that these are the only acceptable values going in. If you only want alphanumeric characters, you could set this to [^a-zA-Z0-9]. If you remove the ^, it means these are the only characters that are unacceptable. Row.ProductNumber shows you the column that you're setting in the Script Transform.

As you can see in this simple example, with roughly a dozen lines of code, you can solve a fairly common business scenario. Here's the final code that is fully integrated.

-- Brian Knight

SQL Saturday #4 in Orlando Between Techeds

2008-05-25T21:12:27Z

If you're going to be in the Orlando area around the TechEd timeframe, I'll be doing speaking at SQL Saturday #4. On Saturday, June 7th, I'll be doing a session on what's new in SQL Server 2008 and on Sunday, I'll be doing a half-day session on how to implement BI with SQL Server 2005. SQL Saturday will be at the Orlando Convention Center between the two TechEd events and is free to attend. We're expecting about 1,200 attendees and I'm only one of many sessions. You can see all the sessions and register at http://www.sqlsaturday.com/.

Speaking at Midlands PASS May Meeting

2008-05-15T17:33:32Z

Are you in Columbia, SC or Charlotte? If so, I hope to see you at the Midlands PASS meeting on the 27th of May.

Tuesday, May 27, 2008

Speaker: SQL Server MVP Brian Knight

The Midlands PASS chapter will hold a special meeting on Tuesday, May 27, 2008, to host SQL Server MVP Brian Knight. I will be giving a presentation on Data Mining using SQL Server. The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the presentation between 6:30 and 6:45 PM. I will send out an agenda next week.

Please feel free to forward this to anyone who you think would be interested in attending. If you plan on attending, please RSVP (BKelley{AT}AgFirst.com) (replace {AT} with @) as soon as possible so we can ensure we have enough space and food.

-- Brian Knight

SSIS Case Sensitivity

2008-05-09T00:11:52Z

One of the most frustrating learning curves for a DBA going to SSIS is the case sensitivity of the environment. Many developers are quite used to this due to option explicit .NET programming languages. The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you're not careful.

One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit. To resolve this, you may have to use an UPPER or LOWER function to make everything upper or lower case. You can also create a new column in the data flow that has the data as UPPER by the use of a Derived Column Transform. There are many options there keep in mind. In the Lookup Transform, you can do upper case the data by changing the SQL query as shown below if the City column is the one where the comparison is happening.

The expression language is also case sensitive. Let's take the conditional logic as shown below:

STATE == "FL" ? "Florida" : STATE

If the state of "Fl" comes through, it will not qualify to be changed to "Florida". Instead, you must make a like comparison as shown below:

UPPER(STATE) == "FL" ? "Florida" : STATE

Additionally, a more obvious case sensitive issue is with variables. All variables, whether in expressions or a script component are case sensitive.

-- Brian Knight

SQL Saturday #3 Jacksonville Recap

2008-05-06T12:46:05Z

I'm still coming down from my high of hosting my first SQL Saturday in Jacksonville at University of North Florida. We had almost 400 people registered and nearly 300 came on the day of the event. The 20 volunteers we had did an amazing job of handling the rush and the day in general. We also had 37 total sessions by 28 national and regional speakers.

If you attended SQL Saturday #3, expect to receive an email from us soon with the various slide decks. Since space was slightly cramped in the building, we had a bit of a big geek hippy picnic in the courtyard shade. It was a neat sight to see all the introverted geeks, socializing. What amazed me most was that we had 100 people at the after event with a live band and pool tables. We had planned on seeing maybe 50-60 there!

As we read the evals, it seems our biggest lessons learned was to give better directions to the facility but other than that, attendees had a fantastic time.

Tim Mitchell from Dallas was one of the speakers and attendees and was kind enough to post almost 100 pictures from his SQL Saturday experience. You'll see pictures that follow the order of: registration, sessions, lunch, closing ceremony and the after event.

http://tim-mitchell.spaces.live.com/

The fanatic SQL Server winner of the day must be Rodney, who has added a SQL tattoo on his left arm.

Thanks to all the sponsors, volunteers, speakers and attendees that made this day possible.

--Brian Knight