Brian Knight, SQL Server MVP

Blog relocating

This blog is making a small relocation in order to consolidate my writing. You can see all future posts here: http://pragmaticworks.com/community/blogs/brianknight/default.aspx

DTS xChange Download Available

The built-in wizard to migrate DTS packages is approximately 40% successful and does not allow you to enjoy the benefits and best practices of SQL Server 2005 Integration Services (SSIS). DTS xChange is a product to help you migrate from SQL Server 2000 DTS to SQL Server 2005 or 2008 SSIS. It does this by applying a series of rules and best practices to your old DTS packages as it migrates the packages. The product was engineered to migrate thousands of packages with little intervention. DTS xChange is broken into three components: profiling, migration, and enterprise logging.

The program is now released and you can download a trial that will convert up to 10 packages (2 packages each time you open the program, up to 5 times) for free and profile all of your DTS packages.

See more information and download the 32 bit trial here: http://www.pragmaticworks.com/dtsxchange.htm

File Properties Task

This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.

More information can be seen here: http://www.pragmaticworks.com/FilePropertiesTask.htm

Migrating DTS packages to SSIS

I've been working for years on developing patterns for migrating DTS packages to SSIS. Finally, we're in the final few weeks of QA work on a new product called DTS xChange that will migrate DTS packages to SSIS and apply best SSIS practices to the package. This product also migrates items that the existing wizard will not migrate such as Dynamic Properties Tasks and connections that use UDL files. It also applies a series of SSIS rules during the conversions like turning on logging. See more information here: http://www.pragmaticworks.com/dtsxchange.htm

Dead Scare

My film crew for Jumpstart TV sent me this after having fun one afternoon. I thought I'd share it with you all. It's done in the style of a government propaganda video on how you can protect yourself from the zombie uprising.

http://www.youtube.com/watch?v=hevVfPT2u1k

SSIS Stumper Volume 1

I've started to write a new series called SSIS Stumpers. Each stumper gives you an intermediate to expert-level business problem to solve with SSIS. Sample data is provided and you're asked to load the data in a challenging way or performance tune a package. Each stumper gives you the requirements at first and you can start there or turn the page to see some hints. If that's not good enough, you can see a step-by-step instruction on the next page.

The first one in the series, your given an example where you'll need to unpivot data using canned components in SSIS. You can download this SSIS Stumper magazine for free with all the data and complete project at http://www.endtoendtraining.com/private/misc/downloads.aspx. If you'd like a step-by-step video on how to solve the problem, see my video here:

http://www.jumpstarttv.com/Media.aspx?vid=268

-- Brian Knight

Favorite BI Features in SQL Server 2008

If you haven’t heard yet, CTP 3, which is a public beta of SQL Server 2008 has now been released and can be downloaded at the Connect website. After a bit of unscientific research, here’s my top 5 BI features in SQL Server 2008. It’s ordered in my priority order. It’s important to note that some of these features have not been introduced in CTP 3 yet but will be coming soon.

No more IIS requirement for SSRS! – Finally, there’s not the overhead of having to install IIS as part of the popular reporting tool.
CDC and Merge – While this is a database engine improvement, it will tremendously help your BI effort in SSIS. The ability to do Change Data Capture on a SQL Server table will help warehousing scenarios and reduce your load time by a factor of 9x.
SSRS scalability – The reporting engine has been significantly improved to scale to million row reports. Also introduced is a best of both worlds table and matrix style report called tablix.
SSIS thread scheduler improvements – In some early testing, the thread improvements have netted a 20-70% improvement in data flow performance. The main improvement is going to be with a long list of synchronous transforms.
Authoring Reports in Excel and Word – Thanks to some smart licensing of Soft Artisians’ product suites, you’ll now be able to author a report in Word or Excel. I would expect this to be a pretty big blow to competing reporting platforms since users will be able to write reports in where they spend most their time in.

-- Brian Knight

Free Hotel or Disney Tickets For the June SSIS and Perf Tuning Class

We're coming up on one year in business and that's cause for a minor celebration! Most of you have probably heard that a high percentage of startups fail within their first year - we're pleased to have made it over that hurdle. We've learned a lot of lessons and probably still have bunch more to go, but it's been fun, challenging, and occasionally intimidating. If you ever get the chance to go solo, try it! You'll be out of your comfort zone and it may not succeed, but it's still a great growth opportunity.

Free Hotel Stay or Disney in June

But back to the celebration. Sign up for the June 11-14 Performance Tuning or June 18-21 Integration Services class between May 21 and June 18, 2007, and you can have a choice of a $250 Disney Gift card (bring the family!) or four free nights in a hotel. Just enter GIFTCARD or HOTEL in the customer comments field during check out. This gives you the chance to visit our Orlando training center for about the same cost as attending a local class since we pay for the hotel for up to four nights! Alumni, don't forget you can receive a 10% discount on any of our classes - just ask for it! June is a great time to visit Florida and the evenings are the best time to enjoy the local attractions. Contact us at training@endtoendtraining.com with questions about the offers, limitations, restrictions, etc.

Upcoming Classes

In our last update we announced two new offerings; End to End Business Intelligence and One on One Mentoring. We've had a lot of interest in both and a lot of requests for variations of the One on One Mentoring. We're also working on a free SQL event in Orlando similar to a Code Camp that will be targeted at SQL professionals. If it works out, we may try it in other cities. Below is a quick synopsis of our schedule, you can view the full schedule here.

Integration Services Jun 18, Aug 27, Dec 17

Performance Tuning May 21, Jun 11, Jul 16, Aug 20, Sep 10, Oct 15

End to End Business Intelligence Jul 30

One on One Mentoring Jun 4, June 25, Jul 9

Analysis Services Aug 13

See the full schedule and registration on http://www.endtoendtraining.com

-- Brian

Expert SQL Server Integration Services Released

My latest book just arrived and is on the virtual shelves of Amazon now. Expert SQL Server Integration Services was a book I wrote with Erik Veerman. The book's goal is to take you to the next level of SSIS and assumes you already know the basics. If you like it, please review it on Amazon :). Soon, we'll be putting up a chapter online so you'll be able to get a preview. Half the book is dedicated to data warehousing with SSIS and the other half is building solutions. http://www.amazon.com/Expert-Server-2005-Integration-Services/dp/0470134119

Free SQL Stumpers Book

End to End training is offering a free SQL Stumpers book (a $24 value) for those who receive their schedule monthly. They even pay shipping. It's full of cool SQL Server questions and answers that you would see on exams or interview questions. You of course have to provide your address in order for them to mail you a book. http://www.endtoendtraining.com/public/redirector.aspx?offercode=freestumperv1&sourcecode=WK&adsourceid=A32BA9FA-52FF-4219-89AB-28C2A63DDD51 -- Brian Knight

Upcoming SSIS and SSAS Classes

Untitled 1 table { font-size: 1em; } A:link { color: #265CC0; text-decoration:"underline"; }

I've been busily writing a new SSAS class this month that I'll be personally training it on 4/23. Below is the entire schedule for upcoming classes from End to End Training and you can also contact me for personal training or consulting if you prefer that model. The End to End facility is really amazing and Andy Warren, who manages the facility does a fantastic job. I hope to see you there!

Course Title	Start Date	End Date	Instructor	Order Link
End to End Analysis Services	4/23/2007	4/26/2007	Brian Knight	Register Now
End to End Integration Services	5/7/2007	5/10/2007	Brian Knight	Register Now
End to End Performance Tuning	5/21/2007	5/24/2007	Andy Warren	Register Now
End to End Performance Tuning	6/11/2007	6/14/2007	Andy Warren	Register Now
End to End Integration Services	6/18/2007	6/21/2007	Brian Knight	Register Now
End to End Performance Tuning	7/16/2007	7/19/2007	Andy Warren	Register Now
End to End SQL Server High Availability	7/23/2007	7/26/2007	Brian Knight	Register Now
End to End Integration Services	8/6/2007	8/9/2007	Brian Knight	Register Now

Video: Hacking SQL Server

In this presentation at the Jacksonville SQL Server Users Group, Bayer White plays the part of a developer protecting his application and Brian Knight attempts to hack his SQL Server application using SQL Injection and cross-site scripting. Then, Bayer will show you how to protect yourself from the hacker and then Brian tries again. Watch the video

Expert SQL Server 2005 Integration Services Book

First, I want to thank you for all of your support on my first SSIS book (Professional SQL Server 2005 Integration Services). Thanks to all of you guys, Wrox has approached a few of us about continuing the book and taking it to the next level. Erik Veerman and I are collaborating together in a new SSIS book by Wrox called Expert SQL Server 2005 Integration Services. The book is not a reference book. It's a book about developing SSIS solutions. For example, we have a complete solution on restartability and building a warehouse ETL solution from the ground-up. I hope you enjoy it and we'll be complete with the writing part this month and it will be on the shelves in the April timeframe. -- Brian Knight

Professional SQL Server 2005 Administration on the Shelves at Last

After a long period of writing and banging my head into the keyboard, my newest book is on Amazon. Professional SQL Serever 2005 Administration was written by some a fantastic team from Microsoft and the community. It teaches you how to cluster, upgrade, administrate SSIS, mirror and much more. You can get it on Amazon now for the geek Christmas present to yourself :)

Amazon Purchase

Jumpstart TV No Long Requires a Login!

You no longer need to be logged into http://www.jumpstarttv.com to watch videos. If you haven't visited the site yet, take a look at it. It has 55 SQL Server how-to videos and growing at 3 a week. The Home Improvement channel will launch in the next few weeks and others to follow after that. The site is looking for speakers so if you'd like to share info, email them at info@jumpstarttv.com.