June 2008 - Posts

• Follow Brian on Twitter

  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. Posted Jun 26 2008, 10:15 AM by Brian Knight with 3 comment(s)

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

  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 Posted Jun 26 2008, 09:12 AM by Brian Knight with 1 comment(s) Filed under: SSIS, webcast

• 5 Hours of Free Training - The BI Series

  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 Posted Jun 25 2008, 08:08 AM by Brian Knight with 4 comment(s) Filed under: SSIS, webcast

• Speaking at JSSUG Meeting

  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 Posted Jun 22 2008, 10:22 PM by Brian Knight with 1 comment(s) Filed under: JSSUG

• Bootstrapping a Business

  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 :). Posted Jun 22 2008, 10:13 PM by Brian Knight with no comments Filed under: business

• SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

  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 Posted Jun 04 2008, 08:05 PM by Brian Knight with 3 comment(s) Filed under: SQL Server 2008, Management Studio

• Using RegEx in SSIS to Remove Given Characters

  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 Posted Jun 03 2008, 09:29 AM by Brian Knight with no comments Filed under: SSIS, Script Transform, regex