Hi all,

We noticed last night that episode 2 of our podcast, "CS TechCast", got a few hits over at digg.com. So we wanted to say thanks, and let everyone know that we appreciate any and all feedback. Something like this is not only makes us feel good (they like us, they really like us!), but helps our business out tremendously. Thanks! And remember to tune in each week!

Digg It

I am easily excited, a nice calculator watch can keep me occupied for hours on end, but that aside, the new MERGE statement in SQL Server 2008 makes me a little giddy. Gone are the days of IF....THEN logic to decide whether a row needs to be inserted, updated, or deleted. The MERGE allows you to take care of the logic and the insert all in one shot. What's more, you can compare a entire record set all at once instead of going row by row. Here's is a quick example of using MERGE.

MERGE tbl_address AS current_addresses

USING

(

SELECT customer_objid = address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted

FROM @addresses

)

AS

source_addresses(address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted)

ON

(

current_addresses.address_label = source_addresses.address_label

)

 

WHEN NOT MATCHED THEN

INSERT (address_label, addressline1, addressline2, city, region, country, zipcode)

VALUES (source_addresses.address_label, source_addresses.addressline1, source_addresses.addressline2, source_addresses.city, source_addresses.region, source_addresses.country, source_addresses.zipcode)

 

WHEN MATCHED AND source_addresses.is_deleted = 1

THEN DELETE

 

WHEN MATCHED THEN

UPDATE

SET address_label=source_addresses.address_label, addressline1=source_addresses.addressline1, addressline2=source_addresses.addressline2, city=source_addresses.city, region=source_addresses.region, country=source_addresses.country,

zipcode=source_addresses.zipcode;

The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and the existing data. Finally you can have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED, or delete WHEN MATCHED and some other values indicates delete.

The possibilities are endless and the syntax is pretty clean. Have fun.
Digg It

We have extra segments on SQL from our author interview on SQL queries to our own, real life experts on MS SQL 2008 features, available at cstechcast.com as a MP3 download or a RSS subscription. Whether you do SQL everyday or just need some insight, this is a good episode. This week we chat with John Viescas, one of the authors of "SQL Queries for Mere Mortals". You can find more information from John Viescas at www.viescas.com. In the news, new chips from Intel, Microsoft SMB leases, SkyDrive being used by spammers, a new-old MBR threat, and the end of the IT department? Plus, "The Worst Tech Move of the Week", "Meet your Local User Group", "A Closer Look", and "The Weekly Tech Tip".

Show Notes

Links to the stories discussed in our podcast:

http://www.pcworld.com/article/id,141300-pg,1/article.html
http://www.darkreading.com/document.asp?doc_id=142884
http://www.eweek.com/c/a/Windows/Microsoft-to-Launch-New-Licensing-Option-for-SMBs/
http://www.eweek.com/c/a/Desktops-and-Notebooks/Intel-Makes-Enterprise-Pitch-at-CES/
http://www.networkworld.com/news/2008/010708-carr-it-dead.html

Featured User Group

The featured group this week, in keeping with our SQL theme, is the Colorado PASS camp. This is a joint effort with the three SQL user's groups on the front range to create an intense, two day workshop every year. Hit the web site and get the details at www.coloradopasscamp.org. 

Please come back again and tell all your friends to download the CS Techcast.

- Eric Beehler (consortioservices.com/blog)

Digg It

After a problem with the local telco, we are back up with our latest at cstechcast.com. We've got some humor and some know how in this episode. A nice balance if I do say so myself. This week we chat with Claudia Baca, author of "Project Management for Mere Mortals". Find her at the web site www.claudiambaca.com, where you will find additional information including white papers and case studies. Also, check out the video series Project Management for Mere Mortals(R) (Video LiveLessons). In the news; a new Firefox vulnerability, Dell and Sun team up, Office 2008 for the Mac, and Hitachi drops 1.8 inch and smaller hard drive lines. Plus, "The Worst Tech Move of the Week", "Meet your Local User Group", "The Tech News Flash Forward", and "The Weekly Tech Tip".

Show Notes

Links to the stories discussed in our podcast:

http://googlewatch.eweek.com/content/google_vs_facebook/scoble_unmasked_as_facebook_digibomber.html
http://msmvps.com/blogs/bradley/archive/2007/11/28/still-having-issues-post-office-2003-sp3.aspx
http://www.reuters.com/article/technologyNews/idUST22530420080104
http://macdailynews.com/index.php/weblog/comments/15941/
http://www.eweek.com/c/a/Storage/Dell-to-Offer-Suns-Solaris-OpenSolaris-in-Servers/
http://www.pcworld.com/article/id,140997-page,1/article.html
http://www.informationweek.com/story/showArticle.jhtml?articleID=205208536&cid=RSSfeed_IWK_All
http://blogs.technet.com/tarpara/archive/2008/01/02/office-2003-sp3-legacy-file-formats-disabled.aspx

A quick update on the Office 2003 service pack 3 issue with blocked files. Microsoft is backing down on it's overarching file blocking stance of older files as detailed in this article: http://www.eweek.com/c/a/Windows/Microsoft-Backs-Down-over-Office-2003-SP3-File-Blocking/. Find a tersely worded explanation of of why the file blocking was put in place at this Technet blog post: http://blogs.technet.com/tarpara/archive/2008/01/07/office-2003-sp3-the-facts-and-fiction.aspx.

Featured User Group

Our featured user group is the Colorado Springs SQL Server User Group. They focus on Microsoft SQL Server technology and the group is headed by SQL MVP and CS Techcast member Eric Johnson. Come on out and get involved. Find them at springssql.org.

As always, leave your comments at this blog post and check back every week for a new episode.

- Eric Beehler (consortioservices.com/blog)

Digg It

We may not have the smooth vocal sounds of Tay Zonday, but we do have a great, new techcast that fits in a tidy half hour of your day available at cstechcast.com. This week we chat with Michael Miller, author of "Googlepedia: The Ultimate Google Resource". In the news, a new Microsoft Security Blog, security breaches are up, and a bad Santa virus. Plus, "The Worst Tech Move of the Week", "Meet your Local User Group", and "The Weekly Tech Tip".

Show Notes - Links to the stories discussed in our podcast:
http://www.darkreading.com/document.asp?doc_id=141991
http://www.darkreading.com/document.asp?doc_id=141264
http://www.eweek.com/article2/0,1759,2241465,00.asp

Here is the new Microsoft Security Vulnerability Research & Defense security blog discussed in our news segment: blogs.technet.com/swi/

Our featured user group is the Queensland SQL Servers User Group in Australia found online at www.qssug.org. Visit and network with your fellow tech heads in person.

Leave your comments at this blog. Happy new year and thanks to all for listening.

-Eric Beehler (consortioservices.com/blog)

Digg It

Behold the holiday edition of our podcast at cstechcast.com. This week we chat with Steve Jones from SQLServerCentral.com about the new compression features in SQL Server 2008. Make sure you check out the web site for MS SQL help and news. In the news, Google not as cool as we thought, IE 6 security hole, new releases, and malware is becoming harder to deal with. Plus, "The Worst Tech Move of the Week", "Meet your Local User Group", and "The IT Pet Peeve".

Show Notes - Links to the stories discussed in our podcast:

http://www.heise-security.co.uk/news/100900

http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9053940&intsrc=hm_list

http://www.eweek.com/article2/0,1759,2237467,00.asp?kc=EWRSS03119TX1K0000594

http://entmag.com/news/rss.asp?editorialsid=9348#6

http://www.news.com/8301-10784_3-9835823-7.html?part=rss&subj=news&tag=2547-1_3-0-20

http://blogs.technet.com/matthewms/archive/2007/12/19/announcement-tafiti-goes-shared-source.aspx

http://blogs.msdn.com/ie/archive/2007/12/19/internet-explorer-8-and-acid2-a-milestone.aspx

http://www.extremetech.com/article2/0,1697,2236683,00.asp

Our featured user group is the Indiana Windows User Group at

www.iwug.net. Check out the web site and show up to a meeting if you're in the area. Now's the time because Server 2008 is just around the corner.

Thanks to Eric Johnson for turning my flub into this week's title. Leave your comments at the blog. Thanks all!

-Eric Beehler (consortioservices.com/blog)

Digg It

The latest podcast episode with information for IT pros is posted at www.cstechcast.com, also available at iTunes and Podcastalley. This week we chat with the authors of "Virtual Honeypots", Niels Provos and Thorsten Holz, about using honeypots to help learn more about Internet attackers. In the news, going off the Rails, exploiting the JET engine, and the early release of Hyper-V. Plus, "The Worst Tech Move of the Week", "Meet your Local User Group", and "The Tech Tip of the Week". After you listen to the interview, get the book: Virtual Honeypots: From Botnet Tracking to Intrusion Detection. Also, catch the latest information at their blog; honeyblog.org. The web site www.honeyd.org documents the Honeyd project.

Show Notes - Links to the stories discussed in our podcast:
http://www.cio.com.au/index.php/id;899402599
http://www.news.com/U.K.-survey-IT-managers-blamed-for-staff-malaise/2100-1022_3-6222631.html?tag=cd.top
http://www.news.com/Survey-People-skills-valued-over-those-for-IT/2100-1022_3-6222828.html?tag=cd.top
http://www.forbes.com/markets/economy/2007/12/13/microsoft-virtualization-closer-markets-equity-cx_cg_1213markets41.html
http://entmag.com/news/article.asp?EditorialsID=9340
http://entmag.com/news/rss.asp?editorialsid=9325#10
http://www.pcworld.com/article/id,140493-page,1/article.html
http://www.eweek.com/article2/0,1759,2231718,00.asp
http://techdirt.com/articles/20071212/171816.shtml

This week, our featured technology user group is the Australian SQL Server Users Group. You can find them on the web at www.sqlserver.org.au. Its worth a view, even if you don't live down under, because they have a news, articles, and podcasts available. Get out in the real world and get involved with your local user group.

Thanks for those who listen and who keep coming back. We do this with no sponsorship and just an interest in bringing you our take on technology. Tune in every week and leave your comments at this blog post. Merry Christmas.

-Eric Beehler (consortioservices.com/blog)

Digg It

Episode 2 of CS TechCast is posted and ready for your consumption at www.cstechcast.com. Here you will find notes and links on the topics discussed in CS TechCast # 2. This week we talk with Bill Inmon, the Father of Data Warehousing, about his now book "Tapping into Unstructured Data". In the news, new stuff from Microsoft, a new old DNS flaw, Sun goes virtual, compliance audits, and IT spending. Plus, "The Worst Tech Move of the Week", "IT Shout Out", and "The Tech Tip of the Week".

Please visit Bill Inmon's web site at www.inmoncif.com. In addition, pick up the latest book from this definitive authority, Tapping into Unstructured Data: Integrating Unstructured Data and Textual Analytics into Business Intelligence, due out in a couple weeks.

Show Notes

Here are link to stories discussed during our news and other segments:

http://entmag.com/news/rss.asp?editorialsid=9308#10

http://www.eweek.com/article2/0,1759,2228177,00.asp?kc=EWRSS03129TX1K0000610

http://www.eweek.com/slideshow/0,1206,a=220909,00.asp

http://www.eweek.com/article2/0,1759,2228717,00.asp?kc=EWRSS03119TX1K0000594

http://entmag.com/news/rss.asp?editorialsid=9307#5

http://www.pcworld.com/businesscenter/article/140340/global_tech_spending_growth_to_slow_in_2008.html

http://www.baselinemag.com/article2/0,1397,2228885,00.asp

http://www.eweek.com/article2/0,1759,2227513,00.asp?kc=EWRSS03119TX1K0000594

http://www.pcworld.com/article/id,140268-pg,1/article.html

It looks like the DNS flaw we discussed has not been addressed in the latest round of patches. For now, all you can do to protect yourself is to disable Auto-discovery in Internet Explorer or other browsers that use this feature, but see the full Microsoft Advisory 945713. The complete list of December patches can be found at the Microsoft Security Bulletin Summary for December 2007.

Our user group spotlight this week is Pacific IT Professionals at https://owa.consortioservices.com/exchweb/bin/redir.asp?URL=http://www.pacitpros.org. Get involved with your local technology community and learn from each other!

Leave your comments about our podcast at this blog post. Thanks for listening.

- Eric Beehler (consortioservices.com/blog)

Digg It

I am blogging today from a little bar in Denver International Airport on my way to Redmond, WA, the mothership for users of Microsoft technology. I have been asked to help develop the objectives for the SQL Server 2008 MCTS Exam. I will post more when I know more, and when I know what I can share. At this point, just know that you will be able to blame me, at least in part if you don't like the new exam. I hope that won't happen, but I am just covering my bases :)

Eric

Digg It

Anyone who has spent time analyzing someone else’s SQL code has had the horrific experience of trying to make sense out of poorly formatted (or completely unformatted) code; indiscriminate case usage, lack of tabs or indentation, and wrapping text with no carriage returns are just a few of the readability problems you can encounter. This is enough of a challenge when trying to determine the purpose of a given piece of code. Then you have to factor in that the SQL you are reading was more than likely written to take function, syntactically, with a specific RDBMS, such as Microsoft SQL Server, DB2, or Oracle. Additionally, each of these implementations has its own set of “current” syntax conventions, as well as older conventions supported purely for “backwards compatibility”. Given this, it can take hours to evaluate what the code is even trying to do, much less figure out how to fix it, much less performance tune it.

Obviously, one of the major purported solutions to this problem is to always use ANSI standardized SQL.

/*

For the uninitiated, ANSI is an acronym for the American National Standards Institute, an organization in the US that sets technical standards. They have published a series of standards relating to SQL in the last few decades, notably SQL-92, SQL:1999, SQL:2003, and SQL:2006. Each version generally includes updates to the standard that reflects new functionality, i.e. SQL:2006 introduced standardization around concurrently accessing traditional SQL data as well as XML data inside a relational database. ISO (the International Standards Organization) generally ratifies these standards as well, since ANSI is a US based organization.

*/

Since many of the major RDBMSs meet at least ANSI-92, if not later, using pure ANSI standard syntax will work for queries that do literally nothing but SELECT, UPDATE, INSERT, or DELETE data. That is, ANSI is used to standardize ONLY the data access and manipulation aspect of what DBAs and developers tend to think of as “SQL”. However, that standard does NOT include anything procedural, such as control-of-flow syntax. This is where the usage of ANSI SQL starts to break down.

Try to remember some of the recent SQL you’ve written. Was there a CONVERT statement? How about a FOR or WHILE loop?

None of these are ANSI compliant; they are extensions supported by specific vendors.

Now, can you imagine a world where none of your SQL had any procedural extensions? You’d be returning raw recordsets back to applications for sorting, traversing, etc. Doesn’t sound like much fun, does it?

Additionally, learning and using vendor specific SQL extensions can have a huge impact on performance. There are always nuances in query design that you can take advantage of when customizing your SQL for a specific RDBMS. But remember, the trade-off is portability and maintainability. If you use ANSI SQL, your code will likely run on any RDBMS with little or no changes. Also, *most* other developers will be able to understand your code, at least at a basic level, without having to dive into the vendor documentation.

So, like any other debate, there are multiple sides, and the answer is always “it depends”. But this is a choice that all database developers should consider when writing new SQL code. Be sure to evaluate each project and environment accordingly to choose what’s best for that situation.

I'm interested to hear what people think on this topic; we've all heard/read about various opinions on this, but what are the people in the trenches actually doing? Post a comment and let me know what you think!

Digg It

Episode 1 of CS TechCast is posted and ready for your listening pleasure at cstechcast.com. This week our guest is Anil Desai, who we talk with about virtualization best practices. In the news, detecting wireless intruders, HP buys up more companies, Quicktime exploit, Exchange Server 2007 SP1, and how to keep your IT staff happy. Plus, “The Worst Tech Move of the Week”, “IT Pet Peeve”, and “The Tech Tip of the Week”.

Here are the show notes.

Links to stories discussed during the show:

http://www.news.com/No-slowdown-in-HPs-software-expansion-quest/2100-1014_3-6220699.html?part=rss&edId=3&tag=2547-1_3-0-20&subj=news

http://www.computerworld.com.au/index.php/id;1359088162;fp;16;fpid;1

http://www.pcworld.com/article/id,140137-pg,1/article.html

http://www.eweek.com/article2/0,1759,2224485,00.asp?kc=EWRSS03119TX1K0000594

http://entmag.com/news/rss.asp?editorialsid=9284#6

http://www.news.com/U.K.-governments-lost-data-worth-billions-to-criminals/2100-1029_3-6220725.html?part=rss&edId=3&tag=2547-1_3-0-20&subj=news

Thanks again to Anil Desai for being our guest. Find his web site at http://anildesai.net and learn more. Be sure to check out one of the many books he has authored.

Post your comments on our podcast at this blog post. Hope to hear from you and look forward to bringing you a new episode every week.

- Eric Beehler (consortioservices.com/blog)

Digg It

We are starting a weekly podcast series called CS TechCast. Check back here regularly for show notes and to leave your comments on each episode. Tune in each Wednesday to http://www.cstechcast.com for new episodes.

Digg It

Back in the days of the original MCSE certification, you needed to practice. In order to practice, you needed machines. Lots of them that you could tear down and build back up. This wasn’t necessarily an easy thing, especially if you were doing a certification run with your own buck. I built two machines out of the cheapest parts I could get away with and still run Windows NT to have a lab to practice on. These days, with the power of our trusty computers, they actually will support running virtual machines.

What is a virtual machine? Well, it is an environment setup by special software that allows the operating system to run without being the only operating system running. In this case, Vista will be blissfully unaware that it is running on top of your Windows XP machine where you just got done blasting out some aliens. For situations where you need multiple machines to test and practice, deployment and event forwarding being two examples, then you can just turn to your smoking hot machine to do all the dirty work and you or your wallet don’t even need to break a sweat.

There are several options to run virtually with a continuing thriving community thinking of new ways to put many virtual machines into one physical box. For your needs, I would recommend one of the options that run on top of your existing operating system. These options include the recently released Microsoft Virtual PC 2007, Microsoft Virtual Server 2005, VMWare Workstation (there is a charge) and Player (free but requires a pre-built virtual machine). There are also products that run underneath the operating system, called a hypervisor, that are more appropriate for servers. If you want to try one out, give Citrix XenServer Express Edition a go.

For my purposes and while writing my MCITP Vista books, I used Microsoft’s Virtual PC 2007. It installs just like any other program on your Windows XP or Windows Vista (not home editions) desktop operating system. The best thing about it is its price, free. For someone on a budget, that has to be the best price of all. Anyway, you’ll want to have a decently beefy system to run a VM, particularly in the RAM department. I’d recommend no less than 1GB, which now seems to be the desktop/laptop standard. Put 2GB in and you will have a much smoother ride. If you’ll be using your host OS at the same time, a dual-core processor helps since the VM will only take advantage of one of those cores, leaving the other core free to download the latest Ricky Martin tracks.

The interface will seem deceptively simple. Above is a screenshot of my Virtual PC Console. To get started, just click File | New Virtual Machine Wizard. All of its virtual machines are based on .vmc files. You can use these to setup multiple boxes with the same settings. My purposes don’t get that fancy, so I just select Create a virtual machine.

Pick a name and save the .vmc file to another location if you choose, I just go with the default.

Microsoft Virtual PC 2007 includes native support of Windows Vista as well as several other operating systems. If you just have to get that DOS game working again, here is your chance.

The next screen will ask you for the amount of RAM you want to dedicate to the VM. I usually go with the recommended amount since I won’t be doing much heavy lifting with the VM, I just need to play with it until it doesn’t work anymore.

Here is the answer to the question, where do all the files go for this virtual machine? They exist in a file called a virtual hard disk. Create a new one, or point to an existing disk. Microsoft and some partners make VHD’s available for download and evaluation. Pick “A new virtual hard disk” when starting out.

Now you get to decide how big the VHD will be and where to put it. This is entirely up to you since you know your machine better than I, but remember that you will likely not be loading all of your applications and other fun onto this VM, just Vista so you can tinker. 20GB is plenty for Windows Vista if you are doing nothing else but practicing with the machine.

After you setup your VM, it will show up in the Virtual PC Console. From here, you can access the settings for your VM ad change things you deem necessary.

This is only the beginning. Now that you’ve built the VM, you essentially have a fresh computer with no operating system installed. How do you get Vista on this thing? Just click Start in the Virtual PC Console. From the CD menu you can capture an optical drive on your computer or an ISO image. After you do this, you’ll be able to boot the machine and start the setup process. I personally like to use the ISO image because it is just a little easier and faster. You can capture your DVD copy of Vista to an ISO using many CD Burning suites, like products from Nero or Roxio or some free alternatives.

That’s really it. Now you’ll have a fully functioning Vista machine without having dropped the dough to buy new hardware. You didn’t even have to upgrade your own computer’s host operating system if you didn’t want to. You’ll also be able to connect it to the existing network or create a private network between your virtual machines via the Console’s settings menu. Just select Networking and choose Shared Networking (NAT) (KB833134).

Some extra pointers. When you first get started and you click your mouse inside the VM window, you will likely be stuck there. Hit the right ALT key on your keyboard to return control to your host. Most shortcuts in VM PC rely on the right ALT key. Virtual PC will whine about the Virtual Machine additions. You can’t install this until the operating system is installed, but it is necessary to get rid of that annoying mouse control problem. You can install it from Action | Install or Update Virtual machine Additions. I hope this makes it easier to get going on your quest to be a fully certified MCITP.

- Eric Beehler (www.consortioservices.com/blog)

Digg It

Tech Mentor Fall 2007 in Las Vegas was a fun experience. A conference built for Windows Administrators, it was a nice change of pace from some of the developer centric shows. We covered so many new technologies, from virtualization to group policy to Powershell, that it made my head spin. Even the general keynote on Windows Server 2008 by Mark Minasi covered so many topics that are new to this OS, you likely couldn’t keep up when taking notes. What was confirmed for me is that a Windows Administrator has to be a jack of all trades. Sometimes the simplicity of Windows lulls employers into a false sense of security that a Windows infrastructure is easy to manage and that you can get cheap labor to cover yourself. Windows professionals know that properly administering Windows is just as difficult as the same environment on another platform. On top of that, some other “OS” experts don’t have to be familiar with as many technologies as their Windows brethren. Little do these people know that when you do Windows support, you can expect any Microsoft product to be thrown your way. You may have to become an expert on ISA as well as Exchange. You don’t see the Cisco security expert managing messaging servers on top of firewalls very often. You may have to manage your DNS environment as well as be good on SMS. When does your UNIX DNS guru have to touch desktop application upgrades?

Don’t fool yourself or let others be fooled into thinking that technology based upon Windows is easy. It is certainly not. With Windows 2008 Server coming and major rollouts of Windows Vista in the near future for most companies, you need to be more engaged in new technology than ever before while still maintaining the job you do now. Employers should not take for granted the deep level knowledge their Windows administrators have and those administering Windows should never rest on their laurels. You are only going to have to dive deeper in 2008.

Digg It

Hey-O!

Eric Johnson and I (Josh) are doing a webcast in conjunction with CA and Redmond Developer News on Data Modeling. To register for the event, click here. Below is the description. Come on by; we'll be talking about why data modeling is important, covering data model basics, and how CA's ERWin tool can help you build data models. Plus, we're trying to work out a deal to give away some sample chapters from our upcoming book, "Architecting Database Models for SQL Server".

--Josh

Understanding and Applying Data Modeling in Application Development

Date: Tuesday, November 13, 2007 Time: 2:00 PM Eastern; 11:00 AM Pacific

About the Webcast: Data modeling is the process of mapping real world information requirements to logical representations of the supporting data. Once your logical model is complete, you can start thinking about transforming it into a physical database model. Keeping the logical and physical model separate, but "in sync" will help ensure that you build a solid database that reflects the business needs. This technical Webcast gives you a thorough understanding of data modeling and shows you how to apply data modeling to application development. This presentation: Takes a close look at entities, attributes, and relationships, and how you can use them to build a logical model. Demonstrates how to take entities, attributes, and relationships and turn them into tables, columns, and keys. Looks at how you can use modeling tools like CA ERwin® DM to help in the process of creating a logical data model and mapping it to a physical database structure. Don’t miss the audience Q&A session following the presentations – always a hit with developers! REGISTER NOW! About the Presenters: Joshua Jones; Co-founder & Operating Systems Consultant; Consortio Services Eric Johnson; Co-founder & Database Technologies Consultant; Consortio Services Danny Sandwell; Product Manager, CA ERwin® Modeling; CA This Webcast is sponsored by CA.

Digg It

My new column on MCPMag.com is up and running. This will end up a regular column on the SQL Pro Insight when that site launches.

http://mcpmag.com/columns/article.asp?editorialsid=2276

Eric J

Digg It

by Eric Johnson

New to SQL Server 2005, SQL Server Integration Services (SSIS) adds an enterprise level extract, transform, and load (ETL) tool to the ever growing suite of SQL Server tools. SSIS replaces DTS, which was only around for two releases of SQL Server and grabbed limited following. Besides being slow and a little tricky to work with, DTS didn’t offer any easy ways of debugging packages when you had problems. With SSIS comes a whole host of methods for debugging your packages; helping you to get to the root cause of your problems.

The environment in which you build SSIS packages is called the SQL Server Business Intelligence Development Studio (BIDS), which comes with the SQL Server 2005 client tools. In reality, BIDS is just a stripped down version of Visual Studio. In fact, if you install Visual Studio on the same box as BIDS, you will, from that point forward, notice that you are running Visual Studio in lieu of BIDS. Okay. So what does this mean for debugging? It means that you have the power of Visual Studio at your fingertips when you are developing your SSIS packages.

Breakpoints

            One of the staples of debugging is the ability to setup breakpoints. SSIS let’s you setup two different kinds of breakpoints; on packages, tasks and containers, or inside of script objects. Once set, the execution of your SSIS package will stop at breakpoints and allow you to view the package in a paused state. When working with breakpoints, keep in mind that they can only be set on items in your control flow; you cannot setup breakpoints on data flow tasks. Let’s first look at setting up breakpoints on packages, tasks or containers.

            To setup a breakpoint on a package, you must go to the Control Flow tab, right-click on the background of the package, and select Edit Breakpoints. If you want to setup a breakpoint for a task or container, right-click the task or container you are interested in and select Edit Breakpoints. In either case, you will be presented with Set Breakpoints dialog.With this dialog, you can pick the Break Condition and the Hit Count for that condition. In this example, I have a data flow task set to break when it receives the OnPreExecute event. You notice there are options for hit count also available. In this example, I am telling the package to break after the task in question receives the OnPreExecute event, which is triggered just before the task runs, 5 or more times. When you run the package, it stops and highlights the task on which the breakpoint was triggered. The breakpoint we set earlier has been triggered and you are given the yellow arrow over the active break point. At this point you have access to the all the local variables, any watch variables you have configured, as well as a view of the package’s status as of the break.

            In addition to being able to setup breakpoints on packages, containers, and tasks, you can configure them inside of script tasks. To do this, you open the script in the script designer and either right-click the line you are interested in and select Breakpoint > Insert Breakpoint, or left click once in the left hand margin of the editor next to the line on which you want to break. Like before, the SSIS package will pause when your breakpoint is encountered.

            The sky is the limit on using your breakpoints. You can set them up to trigger if your “error logic” is called more then a few times, or just to be able to see what the package is doing during a problem spot. These breakpoints are stripped off when you compile the package to run from Integration Services, so don’t worry about affecting the production package when it comes time to elevate.

Data Viewers

            So you can setup breakpoints on your Control Flow, but what if you want to troubleshoot problems inside of a data flow? That is where Data Viewers come in handy. A Data Viewer, of which there are four flavors, can be configured on any of the Data Flow tasks. By right-clicking a path (one of the arrows between tasks) and selecting Data Viewers, you are presented with the Data Viewers section of the Data Flow Path Editor. From here, you can click Add and then select a Grid, Histogram, Scatter Plot, or Column chart to be displayed. The type of Data Viewer you pick will be largely dependent on the type of data you are viewing and what you need to know. In this case, I just want to see the data as it exists after the conditional split task. When you run the package, it will actually break at the data viewer and show you the data in the format you selected. At this point I can examine my data to ensure that things are running as expected or I can look for bad data that may be causing additional problems. In order to get the package going again, I can click the “play” button or click Detach. If I click play, the package will continue to execute, and the next time this same task is run, during a loop for example, I will get another data viewer with the current data. If I click Detach, the package will continue to run leaving my data viewer untouched. In this case, if the same task runs again in a loop, I will NOT get a new data viewer.

            SSIS offers several new methods of debugging. Two of the easiest methods to use are breakpoints and data viewers. Both of these methods will offer you a lot of information about the inner workings of your packages. Data viewers and breakpoints just start to scrape the surface of debugging inside SSIS packages, but hopefully this short overview has given you enough insight to be able to get under the hood and fix some of the problems you may encounter in your packages.