Josh's Stuff

How to fix SQL Server Error "Cannot Generate SSPI Context"

2010-01-21T11:01:00.001-08:00

I have gotten this error multiple times and finally found the fix. I always get it when I have a new server up on the network and try to connect back to it from SSMS on another machine. I can't tell you exactly what causes it, since I don't get it consistently across machines. The quick answer is: Change the account that SQL Server itself runs under to LocalSystem.

The complete answer (including how to fix the domain account that the service runs under) can be found here.

Vonage Part 1 - I am a skeptic

2009-03-13T19:35:00.000-07:00

I am an early adopter of almost anything technology related. However, it is 2009 and many of my friends have VoIP and I am still a skeptic. I recently upgraded from my puny DSL connection (6Mbps/384Kbps) to cable at 30Mbps/8Mbps. Because that breaks my bundle with at&t, I decided to drop my voice and long distance with them, and switch to Vonage. A lot of my friends have VoIP, and many of them actually have Vonage and despite all the HORRIBLE reviews on the Internet for Vonage, they all say that it is great, reliable, works as advertised.

So, tonight I picked up a $150 set of Vonage phones at Fry's, free after rebate. Apparently they let me listen to Vonage voicemail directly from the phones. Sounds good. I just signed up for the Vonage service. The first thing that annoyed me is that by buying the phones in the store (rebate or not), I am not able to enter a promo code that I have been planning to use. I get the first month free, but still have to pay activation. My promo would have saved me $15/month for the first three months and free activation ($30+). Total savings of $75 instead of the $25 I saved for the first month. So. . . I guess the great limited time offer I got on the phones wasn't as good as I thought since I am still paying $50 for them, essentially. Sneaky Vonage, very sneaky. And I don't like it. And I am going to tell all my friends that this is how my relationship with you started out. We'll see if it gets better.

Next thing I noticed: If I ever want to cancel service, there is a $39.99 cancellation fee. WHAT? Are you serious? So. . . anytime I want to cancel, I basically owe them for 1.7 extra months of service. THAT IS NOT COOL! Again, I feel like I am being tricked. Don't trick me, I will figure it out, and it will cause me to say bad things about you to everyone I know. I promise you that. I will forgive, but I will never forget.

Now, I haven't connected the service yet. But Vonage hasn't done a whole heck of a lot to make me comfortable yet with these sneaky little tricks that they are pulling. We shall see what happens, and you can believe that I plan to post it here on my blog, ESPECIALLY if it is not good.

You Can’t Specify the Outbound IP Address in ISA Server 2006

2008-08-14T18:45:00.001-07:00

I just spent several hours attempting to set my outbound SMTP traffic from Exchange 2007 to use a specific IP on my ISA Server 2006 external NIC of the 5 available. I wanted to isolate mail traffic to a specific IP address. However, it turns out that you cannot send traffic over a specific IP in ISA 2006 for any protocol, unless you install another NIC with the IP address that you want to send on. Here is a blurb that was posted by someone on Experts-Exchange after they spoke with Microsoft support on this issue (Not linking since you won’t be able to see the link):

'No version of ISA can, to date, select the IP address that will be used for outbound NAT addressing - only inbound as I have mentioned before. This facility WILL be introduced in ISA 2008 either in the beta release or in the full version. Yes, you can use an additional NIC with a public IP and route mail through that connector but long term, this is not a scaleable option'.

Virtual PC 2007 Laptop Install Mouse Hesitation Fix

2008-08-14T10:45:00.001-07:00

If you are running Virtual PC 2007 on a laptop and are experience mouse stutter or hesitation (no matter how much memory or how powerful your laptop), there is a fix. Here is a link to the explanation, but the short version is find the options.xml file on your C:\ drive (in my case it is in c:\Users\Josh\AppData\Roaming\Microsoft\). Add the following section immediately after the last </virtual_network> tag:

<virtual_machines>
<enable_idle_thread type="boolean">true</enable_idle_thread>
</virtual_machines>

Links to Setting up Kerberos for SharePoint 2007

2008-08-11T19:23:00.001-07:00

Posting this for my own future reference: http://bimvp.com/blogs/bsm/archive/2008/07/01/configuring-kerberos-for-sharepoint-2007.aspx

Useful Visio stencil set for SQL Server 2005

2008-08-04T08:11:00.001-07:00

I am putting this here for my own future reference. There is a useful set of Visio stencils for SQL Server 2005 at Visio Toolbox. Here is a link to the page that they are on, and a direct link to the .zip file. These stencils appear to have actually been created by Microsoft directly, since the page is on a Microsoft website.

There is also a page on the site that shows how to work with Visio and SQL Server. There are several videos and a couple of downloads.

Post SR1 Rollup Package 1 for Data Dude available

2008-07-29T13:56:00.001-07:00

If you are using Visual Studio 2005 Team Edition for Database Professionals (Data Dude), you might be surprised to know that there is a post-SR1 Rollup Package 1 update available. It can be download here. The list of fixes can be found here, and I have pasted it in below:

Inconsistent revalidation of database object definitions when you close and then reopen a Visual Studio database project

A database project cannot be deployed if the project contains interdependent inline functions

If you use a four-part name to reference a table, you may receive an incorrect syntax exception

Database Professional Interpreter does not handle the "sp_executesql" parameter correctly

In a Visual Studio 2005 database, you cannot import a schema that was created by using the SQL Server 2000 compatibility mode

Error message when you use Design-Time Extensibility (DTE) objects: "Do you want to reload"

Cross-database references cause slow performance when you load a project

Schema Compare crashes the Visual Studio IDE when databases contain different versions of assemblies

Schema Compare does not correctly generate the update script for a stored procedure

The schema script is not generated when triggers or views that have the same name exist

Error message when you build a deployment script: "MSB4018: The 'SqlBuildTask' task failed unexpectedly"

After Schema Compare obtains the latest project files from Source Control, Schema Compare indicates that databases are different

How to fix Hyper-V error “Failed to add device Microsoft Synthetic Ethernet Port”

2008-06-14T14:55:00.001-07:00

Hyper-V Beta, and RC0 worked great. I recently upgraded to RC1, and in the process of both trying to move a VHD from another server and trying to create a new VM from scratch I repeated received the following error:

Failed to add device ‘Microsoft Synthetic Ethernet Port’

Here’s what I figured out: The Microsoft Forefront Client Security that was running on the host machine was interfering. Once I excluded both .XML and .VHD files in Forefront, everything started working again. That was an easy fix.

SQL Server 2008 RC0 is available for public download

2008-06-10T11:54:00.001-07:00

Surprisingly, I haven’t heard much buzz around this, but SQL Server 2008 RC0 is now available for public download at this link. I haven’t had a chance to look at it yet, but supposedly this is the final release before RTM later this year.

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

2008-04-03T11:58:00.001-07:00

My buddy Wayne just brought to my attention a white paper that came out just a year ago from Microsoft that describes how to design SSAS 2005 cubes for use with Excel 2007. I'm posting it here for my own future reference. Here is the description:

Overview

Microsoft Office Excel 2007 takes advantage of most of the features in Microsoft SQL Server 2005 Analysis Services. To take full advantage of these features, it is important to keep in mind the end-user experience in Office Excel 2007 when you are designing cubes.
This document outlines how you can create a good end-user experience by optimizing the cube design for Office Excel 2007 PivotTable dynamic views.

Proper Way to Redirect OWA Users via ISA Server 2006

2008-03-31T09:52:00.001-07:00

Oops, an update to my last post about redirecting OWA users when deploying Exchange with ISA. Here is a blog detailing the "correct" way to do it, and also how to redirect http to https, etc.

Redirecting OWA URL with ISA Server 2006 (Quick and Dirty way)

2008-03-31T09:49:00.001-07:00

In setting up Exchange 2007 OWA through ISA Server 2006, I realized that it would be a lot easier to tell users to access it via http://owa.domain.com as opposed to http://owa.domain.com/owa (which is repetitive if nothing else).

Problem is, there is no obvious way to do this in ISA or Exchange. Fortunately I am not the first one to have this problem, and it has already been solved. The short version of the fix is to set OWA up 100% according to whatever book, article, white paper you are already following. THEN, add another web access rule point to the same site, except this time set it to Deny. You don't have to get too fancy on the various settings, authentication, etc. and just use the same listener that you created for OWA.

Once the rule is created, make sure it is the one just above the main OWA access rule (I named the new one "Redirect OWA" so I would know what it was), and then go back in and on the deny page, set it to Redirect to your specific OWA subdirectory (http://owa.domain.com/owa). It works great!

Here are the two resources that I found to be most helpful:

Thomas Shinder's Blog

Blog du Tristank (look in the comments)

**UPDATE** I have since found a blog that tells the correct way to do this (but the quick and dirty listed above still works fine). Here is a link to my blog post about the correct way.

SSIS Performance Counters don't work on x64 (out of the box)

2008-03-20T11:16:00.001-07:00

If you want to spend some time tuning performance of your SSIS solution, it is likely that at some point you will leverage the SSIS performance counters found in the SQL Server:SSIS Pipeline object. However, if you are using 64-bit SQL, you will not find the performance counters listed in PerfMon. That is because here is a known bug in SQL Server 2005 x64, which you can read about in KB 941154.

What is the problem? The short version is: When SQL is installed, it accidentally points to the \Program Files (x86)\ directory for the SSIS PerfMon counters, except that they are actually in the \Program Files\ directory.

How do I fix this? Its easy, but requires a reboot. Here are the instructions, straight from the KB linked above:

To work around this problem, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. In Registry Editor, locate the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\DTSPipeline\Performance\Library

3.Double-click the Library registry entry.

4.In the Edit String dialog box, type the following string value in the Value data box:

DriveLetter:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSPipelinePerf.dll

5.Restart the computer.

UPDATE: This fix does not work as described in the KB

Even though there is a DTSPipelinePerf.dll file at the path given in KB 941154, simply pointing to it in the registry setting and rebooting does not make it visible. I was able to get it to work using Plan B: change the path back to \Program Files (x86)\ and use the 32-bit version of PerfMon.

There are two ways to use the 32-bit version of PerfMon:

1.) Start | Run | mmc.exe /32 perfmon.msc

2.) Execute c:\windows\sysWow64\Perfmon.exe

Programmatically executing SSIS packages

2008-03-17T13:20:00.001-07:00

For a good summary of many different ways to run an SSIS package programmatically, as well as advantages and drawbacks of each, look here at Michael Entin's blog. There are some good things in the comments as well. This will come in handy at some point, I am sure.

Setup and Administration FAQ's for TFS 2008

2008-03-10T12:06:00.001-07:00

I found a good resource for FAQ's for TFS 2008 on the MSDN forums and am posting it here for future reference. Here is the link

Upgrading Existing SSIS Packages to TableDifference Component 2.0

2008-03-05T14:35:00.001-08:00

I am an avid user of Alberto Ferrari's SQL Server Integration Services "Table Diff" component, which you can download from http://www.sqlbi.eu. He recently released the new, improved version 2.0 of the component (the .dll still says that it is 1.0 though?) and I am upgrading a mid-size solution that makes heavy use of the prior version of TableDiff (1.x) to use the new component. Here are my observations of both the upgrade and the new capabilities of the component.

Here is a typical Type 1 style update package (in this case an import table, not a dimension) that uses TableDifference at its core to compare the new and old data flows and direct output accordingly. Behind the scenes, I overwrote the TableDifference.dll file with the new one in both the .NET Assembly Cache and the \PipelineComponents directory. When I opened the package, everything is working except the Update and Insert components have red X's. These are easily fixed by simply double-clicking on each to open properties, accepting the new automatically matched column references and saving the package. That's it, there is nothing else required to update your pacakages from TableDifference 1.x to the newly released 2.0.

Upgrading from TFS 2005 to 2008

2008-03-04T13:39:00.001-08:00

A couple of things about upgrading from Team Foundation Server 2005 to 2008 that I wanted to highlight:

1.) The new version of TFS Explorer does not work with VS 2005. However, you can use the TFS 2005 Explorer to connect to TFS 2008 Server and do 95% of what you need to do. Just install the VS 2008 version on at least one machine to get the extra features if needed, and it will work fine in a side-by-side deployment. If you want the TFS 2008 Explorer, you can download the stand-alone install here.

2.) If you are not doing an in-place upgrade and want to move your source code items and work items (with history), there is a tool on Codeplex that will let you do that (even between TFS 2005 and TFS 2008). You can find it here.

3.) You can download TFS Web Access for 2008 and it presents a very nice UI for interacting with TFS. The Visual Studio interaction is still a little scattered in my opinion.

4.) If you were using Changeset Comments in 2005, you will need to add the TFS 2008 Powertools to get that feature back.

5.) You MIGHT have a problem with adding items to 2008 if you already had 2005 on the same dev machine. I was having problems with phantom workspaces after I deleted them, connections to the 2005 server that wouldn't disappear no matter how many times I deleted them, Can Not Connect To Server errors when I tried to add items to the new 2008 server, etc. HERE IS THE BRUTE FORCE ANSWER: run devenv.exe /resetuserdata and everything will work. *Warning* Your personal Visual Studio settings will be erased if you do this, but that is not the worst thing in the world.

Team Foundation Server 2008 RTM and 64-bit

2008-02-29T11:46:00.001-08:00

Bad news - Microsoft is pushing 64-bit (as they should), but one of their newest enterprise products does not yet support it. Team Foundation Server 2008 only supports 32-bit. They have said that 64-bit support will be in the next version of TFS, "Rosario".

At least they support 64-bit SQL and SSAS, but definitely not MOSS 64-bit. We have deployed 64-bit MOSS 2007 and it turns out that it doesn't even support 64-bit SharePoint when SharePoint is running on a different server from TFS, it doesn't even recognize that MOSS is installed:

SQL Server Default Ports

2008-02-28T21:08:00.001-08:00

It seems like it is always hard to track down the ports that SQL Server uses, so thought that I would post them here for when I need them. These are the SQL Server default ports.

SQL Component	TCP Port
SQL Service	1433
SQL Browser Service	1434
SQL Monitoring	1444
SQL Server Analysis Services Redirector	2382
SQL Server Analysis Service	2383
SQL Server Reporting Services	80

Excel 2003 Add-in for Analysis Services

2008-02-28T12:20:00.001-08:00

If your company (like most companies) is still using Excel 2003 and you are implementing a data warehouse which contains SSAS cubes, you might be interested in the Microsoft Excel 2003 Add-in for SQL Server Analysis Services. I have not used this tool extensively, but it does bring Excel 2003 closer to Excel 2007 in terms of OLAP capabilities. Here is the blurb from the download page, I will post more as I spend time with the tool. This is not a new find, nor is it likely news to anybody, but worth noting so I am putting it here.

The Excel Add-in for Analysis Services enables users to access and analyze data from multiple Analysis Services Cubes, and to create rich, customized reports directly in Microsoft Office Excel 2003 or Microsoft Excel 2002. This download can improve data analysis, shorten reporting cycles, and enhance your company's ability to respond to customers.
This download not only reduces the time and resources required to train users, but it also eliminates the need for organization’s to support specialized reporting systems and tools. The key business benefits of Excel Add-in for Analysis Services include:

Visibility in Excel into business trends
Increased speed and quality of decision-making
Streamlined data analysis that shortens reporting cycles and saves resources
Increased reporting flexibility through rich, highly customized, and refreshable reports
Access to relevant information from multiple data sources

About the Excel Add-in for Analysis Services
With the Excel Add-in for Analysis Services, individual users can manage the reporting cycle from beginning to end and eliminate the need to cut and paste data from multiple systems.

Access: Easily create and maintain live data connections to multiple Analysis Services cubes, ensuring data consistency and integrity, and combine data from multiple sources into a single report.
Analyze: Conduct detailed analysis using native Excel capabilities. Extend the richness of analysis through “what if” and drill through capabilities.
Author: Easily personalize and refresh report layouts. Minimize end user training and reduce reliance on IT due to Excel’s popularity and ease of use.

The Data Warehouse Lifecycle Toolkit 2nd Edition is now available

2008-02-27T13:40:00.001-08:00

The first one is a great reference, but was published in 1998. Since then the art and science of building the data warehouse has matured significantly. I don't think that the word BI really existed in 1998. . . Actually I don't know since I didn't start working in the DW/BI world until 1999 with Brio, then Business Objects.

The original had a lot of great material for organizing the pro cess around preparing for, implementing and maintaining the data warehouse, including a nice set of documents. Here is a link to the new book on Amazon.

Installing Exchange 2007 on Windows 2008 (aka UAC is a pain)

2008-02-27T12:00:00.001-08:00

Over the last couple of days I have been setting up Exchange 2007 SP1 on Windows Server 2008 RTM. No matter what I tried, I was stuck in a Catch-22: If I logged in as Local Admin on the box, I didn't have permission to make the necessary changes to the domain; if I logged in as Domain Admin, I didn't have permission to write to the local log file on the C:\ drive of the Exchange server. I kept getting errors like the one at right from the GUI, or this one from the command line:

Failed to initialize the log file: Access to the path 'C:ExchangeSetupLogs\ExchangeSetup.log' is denied.

Setup will not continue.

Those are NOT the most descriptive errors. If I logged in as local admin, those errors went away, but instead I received errors that the local admin account didn't have the rights to make massive changes to the domain (shocker).

I posted on the Microsoft Technet Exchange forums, and hardly anyone read my post, much less answered it. One person responded that they had the same problem, so I knew it wasn't just me. I noticed that I couldn't run the basic commands that install Windows 2008 components, like PowerShell without getting similar errors. It didn't matter if it was on a Hyper-V VM, or a physical server, I could not resolve the problem.

Then I had an idea. I knew that Windows 2008 and Vista are the same underneath, and I remember that the first thing I did when I installed Vista was to disable UAC. . . hmm. . . yep, that was the problem. Here is where you do it on Windows 2008, just like in Vista:

Then go here:

I hope that anyone else that is about to go crazy from Installing Exchange 2007 on Exchange 2008 finds this blog entry and simply disables UAC.

Is the Nintendo Wii just a gimmick?

2008-02-25T19:52:00.001-08:00

I was reading a post on Kotaku about the new Wii 3.2 system software update, and I noticed that about 40% of the comments say something along the lines of "I haven't turned my Wii on since the last system update", or "I only play my 360 or PS3".

I have a 360, PS3 and Wii, and that is the order that I would rate them right now. The Wii is cool, and Wii Sports is the best game ever for when you have friends over. . . but I haven't really seen anything else that is impressive enough to hold the attention of anyone over the age of 25.

I know that they are selling many more Wii's than they are 360's or PS3's, but I bet 80% of them are collecting dust after the first couple of weeks. Microsoft has a crazy high attach rate, something like 8 games for every system sold. I'm guessing the Wii has about 2 games for every system sold. I hope that they come out with a killer app, or at least Wii Sports 2 in the near future.

New Bluetooth Headset - BlueAnt Z9

2008-02-21T08:17:00.001-08:00

My old bluetooth headset died (Plantronics Voyager 510, best basic bluetooth headset you can get for $45). This seems to happen every year in February. I am very picky about my bluetooth headset, so always try a couple until I get the perfect one. This year I tried 2: the Aliph Jawbone and the BlueAnt Z9.

They both claim to have excellent noise cancellation, but the Jawbone is very dramatic about this claim. Back before I blew the engine on my Land Rover, noise was a real problem since that is probably the noisiest car on the road. Since I don't have that car anymore, noise is less of an issue.

The bottom line is that for the money ($75), the Jawbone would have to be the best thing ever. It turns out that it is not the best thing ever. It is awkward to put over your ear and too big to put in your pocket. The BlueAnt is easy to put over your ear and fits in your pocket no problem, plus its only $60. I've had it for a 2 weeks and it is great. They seem to upgrade the firmware frequently, and I have already updated it to v3.4, it seems to be better than it was on v3.2 but it might be my imagination. At any rate, it is a nice headset and I recommend it.

Connecting Excel Services to an External Data Source

2008-02-14T11:08:00.001-08:00

I will be setting up a proper connection from Excel Services to an Analysis Services 2005 cube over the next couple of days and found this article that describes the necessary steps in detail. The article is titled Plan external data connections for Excel Services, just what I was looking for.

Undoing Checkout for Another User in TFS 2005

2008-02-06T13:21:00.001-08:00

It is not as easy as it should be to undo someone else's checkout in Team Foundation Server 2005. In 2008, you can do it through a GUI, but only if you have the Power Tools installed.

Here is how to do it in TFS 2005, from the command line:

tf undo [/workspace:workspacename[;workspaceowner]] 
[/server:servername] [/recursive] itemspec [/noprompt]

I was also faced with having to add my login info, so the exact command I used was as follows:

tf undo /workspace:WORKSPACE-NAME;User $/myProject/CheckedOutFile.cs /login:myusername,mypassword

I also found out that you can delete an entire workspace for a developer who is no longer on your project and maybe had some files checked out. That command is even easier, and will automatically undo any checkouts:

tf workspace /delete Workspace  (add "/login:name,password" to end of command if not using AD)

SQL Server 2005 SP2 Performance Reports

2008-01-31T12:28:00.001-08:00

Just stumbled across a whole set of reports that can be deployed to SQL 2005 if you have SP2 installed. Here is the link. From what I have read, these are a pretty slick addition to the existing reports (you did know that there are existing reports, right?)

Here is the description from the download site:

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:

- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

Microsoft BI Demo VPC 5.1

2008-01-24T07:32:00.001-08:00

You better have a monster machine on which to run this: Microsoft has released a new Business Intelligence sample VPC. The .vmc is set to use 1GB when you first install it, I would recommend bumping to 1.5GB - I tried 2GB and it was a still sluggish on my laptop. Might want to throw it on a spare server if you have one.

It is a big download, but definitely worth it if you want to have a VPC available for "playing around" with any of the Microsoft BI tools without having to go through the trouble of deploying MOSS, etc. on a VPC.

Download Part 1 (3.5GB)
Download Part 2 (1GB)

From the readme, here is the list of software that is installed and fully configured:

Microsoft Windows Server 2003 R2 Service Pack 2
Internet Explorer 7
Microsoft Office Enterprise 2007
Microsoft Office Visio 2007
Microsoft Office Project 2007
Microsoft Office SharePoint Designer 2007
Microsoft SharePoint Portal Server 2007
Microsoft Visual Studio 2005 w/ Service Pack 1
Microsoft SQL Server 2005 w/ Service Pack 2 (w/ SSIS, SSAS, SSRS)
Microsoft SQL Server 2005 Data Mining Add-ins
Microsoft SQL Server 2005 SharePoint Integration Add-in
ProClarity Analytics Server 6.3
ProClarity Dashboard Server 6.3
ProClarity Desktop Professional 6.3
ProClarity Web Professional 6.3
Microsoft PerformancePoint Planning Server 2007
Microsoft PerformancePoint Monitoring Server 2007
Microsoft PerformancePoint Dashboard Designer
Microsoft PerformancePoint Planning Add-In for Excel
Microsoft PerformancePoint Planning Business Modeler
Project REAL Reference Implementation
SoftArtisans OfficeWriter 3.8.1 and Designer
BI Demo Files
Microsoft SQL Server 2005 Samples
Microsoft SQL Server BI Metadata Samples
Performance Point Planning Samples/Labs
Visual Studio 2005 Tools for Office Second Edition

New set of SSIS Tasks

2007-11-26T12:43:00.000-08:00

These may have been around for a while, but I just discovered that there is a nice set of (affordable) SSIS tasks at CozyRoc. There is a task for zipping files, managing partitions, SFTP and even using another dataflow as a source. Here is the current info from their site:

Component
Key Features

Beta! Package SSIS Connection Manager

Gain access to the current or different package at runtime.
Support for all current types of package storage: SQL, File, SSIS Package Store.
Support for package variables initialization before execution.
Easy to use interface.

Beta! Data Flow Source SSIS Data Flow Component

Based on Package Connection Manager.
Very fast, non-blocking, in-memory data read from another data flow.
Scalable alternative of the solution Microsoft is recommending with raw data file source.
User interface is based on the standard data flow source component dialog.

Beta! Data Flow Destination SSIS Data Flow Component

Exposes data from a data flow for in-memory read in another data flow.
Scalable alternative of the solution Microsoft is recommending with raw data file destination.
User interface is based on the standard data flow destination component dialog.

Database Partitions SSIS Control Flow Task

Saves you time by handling the intricacies of the partitioning and letting you concentrate on your workflow instead.
Create, insert and remove database partitions in Microsoft SQL Server 2005.
Combine smaller partitions into a larger one, for easy backup later.
Support for ADO.NET, OLEDB, ADO connection managers.
Easy to use interface.

Script Task Plus SSIS Control Flow Task

Extension of Microsoft SSIS Script Task, meaning you already have the skills to use it.
Greater reusability and easier maintenance of script functionality.
Customizable script user interface and setup.
SSIS Script export, import or linking to.
Keep your script code separate from your package and have easier time tracking changes in your source control system.
Requires SQL Server 2005 SP2.
New! Share your exported SSIS scripts with the community.

Zip SSIS Control Flow Task

Compression and decompression of Zip, GZip, BZip2, Unix, Tar archives.
Automatically switches to Zip64 format, if it is required.
Streamlined user interface.

SSH SSIS Connection Manager

Secure connections with SSH-enabled server.
Authenticate with password or private key file.
Support for Tunnel (HTTP), SOCKS4, SOCKS5 proxy.
Streamlined user interface.

SFTP SSIS Control Flow Task

Based on SSH Connection Manager.
Secure-FTP (SFTP) communications and management.
Support for SFTP commands send, receive and delete files, create and remove directory, rename file.
The user interface covers most of the functionality of the standard Microsoft SSIS FTP task and it is very similar.

SSH Execute SSIS Control Flow Task

Based on SSH Connection Manager.
Secure execution of shell commands on a remote SSH server.
Streamlined user interface.

More Interesting SSIS Components

2007-10-17T09:53:00.001-07:00

I knew about the Konesans Trash Destination, but it turns out that they have a whole series of free, publicly available SSIS components. The main product page is here, and below I have pasted in content for each of the transforms, with the link to the download page:

Checksum Transformation

The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. Checksums can reduce network contention and increase process performance by allowing you to verify data through a single value rather than transferring all data values for comparison.

Data Generator Source

This source component literally generates data. Specify how many columns you want, and how many rows, then watch the data flow out. Build demonstration and research scenarios faster with this simple source.

File Watcher Task

The File Watcher Task does what it says really, it watches a folder waiting for files. When an available file is found the task completes, returning the name of the file for onward use within the package.

Regular Expression Transformation

The Regular Expression Transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

Row Count Plus Transformation

The Row Count Plus Transformation can replace the stock transformation. We have recreated and extended with more functionality and a user friendly interface for faster and easier package design.

Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behaviour within the Data Flow. Uses include surrogate key generation or data partitioning within the pipeline.

Trash Destination

The Trash Destination Adapter is a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.

Additional SSIS Transforms that didn't ship with the product

2007-10-16T12:10:00.001-07:00

There are a whole series of SSIS transforms that Microsoft has made available for download. They have differing degrees of usefulness, and have been available for a while but I am putting them out here for future reference. Off the top of my head, it looks like Regex would be useful, UnPackDecimal if you are deal with mainframe files, calendar transform (as I recall it has a dependency on a DLL from SSAS though).

Regex Transform
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

ConfigureUnDouble Transform
ConfigureUnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes. Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

RTrimPlus Transform
RTrimPlus takes a string or unicode column and removes trailing spaces, whether ASCII or Japanese.

UnpackDecimal Transform
UnpackDecimal takes an input column formatted in packed decimal (comp-3) and generates the corresponding Decimal value.

UnDoubleOut Transform
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place or via the creation of a new output column.

CodePageConvert Transform
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SeeBuffer Transform
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

UnDouble Transform
UnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

NullDetector Transform
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

Calendar Transform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes

Microsoft "Bulldog" (MDM)

2007-09-26T13:24:00.001-07:00

Details are just coming to light regarding Microsoft's entry into the world of Master Data Management through their acquisition of Atlanta-based Stratature several months ago. The product will be released to coincide with the next release of Office (Office 13), and will install as part of SharePoint. According to the Bulldog product roadmap, the dev team is working with many Microsoft products such as PPS, Dynamics, SharePoint, Excel and more to integrate the master data hub with those products. The stated goal is to have Bulldog remain almost identical to the Stratature +EDM product, with several enhancements aimed at improving the experience.

There is now a full product page at Microsoft, and Kirk Haseldon has updated his blog with some of the new details. From his blog, it sounds like the planning has turned to development, and we should start to see something tangible in February 2008 for TAP program members and other early adopters.

One exciting aspect of the product is hierarchy management within an enterprise. Taken directly from the product roadmap, here are some of the hierarchy scenario's that Bulldog will handle:

Master data contains a wide variety of hierarchies. The roll‐up hierarchies for accounts in a chart of accounts, the reporting hierarchy for employees, the organizational structure of a customer’s business, and categories of items in an item master are typical examples of hierarchies. These hierarchies are often hard coded into business systems or defined in a number of Excel spreadsheets. Hierarchies can be defined by attribute relationships: for example, city belongs to state and state belongs to region. These derived hierarchy relationships change whenever an attribute value changes. Hierarchies can also be defined as parent‐child relationships that may have an arbitrary depth along any path (ragged). The Stratature solution allows both types of hierarchies to be leveraged into a new derived hierarchy. The advantage of reuse is that it ensures that there is only one definition of the relationship at any point in time.

There is also extensive support for Business rules, workflow, flexible data models, etc.

Microsoft Performance Point Server 2007 finally RTM's

2007-09-26T07:35:00.001-07:00

After years of hard work and a ProClarity acquisition later, Performance Point Server 2007 has finally been released. More details here on the product home page. Here are links to trial downloads and SDK.

PerformancePoint Server 2007 Evaluation Version (x86)

PerformancePoint Server 2007 Evaluation Version (x64)

Deployment Guide, Operations Guide, Whitepapers

Planning Server, Business rules development guide and Monitoring SDK

Slick New MDX Editor/Debugger

2007-09-20T06:53:00.001-07:00

This is a very nice looking tool for anyone that works with MDX. It is currently in "CTP 1, version 0.1 Alpha" release, which is another way to say "Very early in development cycle". It works with SSAS in all of its various versions from 7.0 to 2008. Details and download here.

Documenting a SQL Server 2005 Solution end to end

2007-08-16T12:01:00.001-07:00

I had heard of this tool before, and it looks interesting if you ever need to document a full SQL Server 2005 solution. It apparently documents SSIS, SQL and SSAS as well as SSRS. The output shows data lineage, as well as the structure of your database. Worth a look, pretty affordable too. Too bad they couldn't make a version that worked as an add-on to Data Dude. Here is the link to their homepage.

Load Testing SQL Server Analysis Services 2005

2007-08-13T12:23:00.001-07:00

Have you ever had a need to load test SSAS and then report on the results? Your life just got easier. There is now a project on CodePlex for that very purpose. Apparently MCS and other large internal projects have used it for a while, but it is now publicly available as a community project. Download it here.

Taking the pulse of SQL Server

2007-08-13T08:03:00.001-07:00

If you have ever been involved in a big SQL Server project, you might have wanted some easy way to figure out how things are performing on the server. Fortunately, many other people have wanted to do the same. There are several ways available now (and more to come in SQL 08, specifically for SSAS I believe). You can use the SQL Server Health and History Tool (SQL H2) which tells you all kinds of interesting things about the internals of SQL and run reports against it.

The other new and cool thing that has come out is an actual data mart that is built on top of the man Dynamic Management Views (DMV) inside SQL Server. The DMV's are what drive the reports inside SSMS, and you can use them to find out all kinds of things. Now you no longer have to run them manually. Thanks to the SQL Server Customer Advisory Best Practices Team, now you can just install SQL DMVStats Data Warehouse on your server and your good to go.

Here are the main features as listed on CodePlex:

• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

VSTE for DB Pros (Data Dude) Power Tools released

2007-08-13T07:44:00.001-07:00

If you are using VSTE for DB Pros, you will probably want to pick up the newly released Power Tools (didn't they used to call these sorts of things "Power Toys"?). It adds a lot to the product in the way of convenience with the dependency viewer (you don't have to pretend to rename something to see dependencies now), additional refactoring abilities, enhanced data generation and T-SQL code analysis among other things. Download it here.

Read more about it at Gert Drapers' blog, including detailed explanations and screen shots.

Here's the list of new features, copied from Gert's blog:

Dependency Viewer
Refactoring
- Move Schema
- Expand Wildcard
- Fully Quality Name
- Refactor in to strongly typed DataSet definitions
- Refactor Command Generator
Data Generation
- Sequential Data Bound Generator
- Editors for the Data Bound Generator, Sequential Data Bound Generator and RegEx String Generator to make configuration easier
- The RegEx editor also tries to interpret your CHECK CONSTRAINTs and create a matching RegEx expression that you can use to generate data values that match the constraint definition
- The RegEx editor can also be used for interactively defining and testing RegEx expressions and evaluate the output visually, which makes it a lot easier to create the right RegEx expression for your value domain.
MSBuild Tasks
- SqlSchemaCompareTask; allows you to compare schemas between two database from the command line using MSBuild.
- SqlDataCompareTask; allows you to compare the content of tables within two databases from the command line using MSBuild.
T-SQL Static Code Analysis
Miscellaneous tools
- SQL script pre-processor command-line utility, which will expand all SQLCMD includes and variable definitions (sqlspp.exe)
Schema Manager API

SR1 for Visual Studio 2005 Team Edition for Database Professionals (DataDude)

2007-08-13T07:38:00.001-07:00

This news is about a month old, but the RTM version of SR1 for VSTE for DB Pros has been been released and is available for download. One of the most important things that it adds is the ability to have "database references", which is a 3 or 4 part reference to a database other than the one that you are currently working with. Much-needed functionality.

SSIS Pivot Transform is harder than it needs to be

2007-08-08T14:02:00.001-07:00

Don't get me wrong - I am glad that they included the pivot transform, and I recognize that the dev team likely ran out of time to implement any sort of UI, but Wow! That thing is way more difficult to use than it needs to be.

For instance, and I don't doubt that it is somewhat user error on my part, but in my first attempt to get it working today with 3 columns (UserID, AttributeName, AttributeValue), the transform coughed, choked and wouldn't start. It just gave me errors. Due to lack of time, I had to set it aside for now. I appreciate its usefulness, but it is painful to use, and due to the limited info on the Internet about it (there are roughly 2 useful articles/blog posts), I am guessing that a lot of others just work around it as well. A shame, since the Unpivot transform has a UI and everything.

In summary - I am not saying it is broken, I am not saying it is not useful. Just that it not only is difficult to use, but that documentation and samples are lacking as well. Here are the few useful posts I found:

Ashvini Sharma's blog

MSDN

Database Journal

UPDATE: My friend Matt pointed me to a blog entry that he wrote while at our old company that is a still up in which he gives some tips on the pivot transform.

Useful information about Active Directory

2007-08-07T10:56:00.001-07:00

Anyone who is new to Active Directory will likely have a hard time finding a single comprehensive source of information on the topic. I think the problem is almost that there is so much information, that it is difficult to find the simplest things. Take, for example, the User Properties dialog. What if you simply wanted to tie the fields on this screen to the LDAP attribute behind each? There is nothing out there (that is easy to find) in the Microsoft documentation that spells this out. Fortunately R.L. Mueller put this very document (and others) into and Excel spreadsheet and RoudyBob posted it to his blog. Now the rest of can benefit. Here's the link to the blog post.

Here are the direct links to the various spreadsheets:

Spreadsheet of User Properties in Active Directory Users & Computers MMC
Spreadsheet of all Active Directory attributes
Spreadsheet of User Object Property Methods
Spreadsheet of attributes exposed by the WinNT provider

Interesting AJAX enabled SSAS client

2007-08-06T14:28:00.001-07:00

I was checking my webmail today, and one of the ad-links caught my eye. It was for an AJAX based SSAS client. I clicked the link and it took me to IntraSight, by a company called Active Interface. Screenshots are available at that link. There are a lot of interesting SSAS web based interfaces floating around out there if you look hard enough. Add this one to the list. SharePoint integration is listed as one of the bullet points. Pricing is pretty affordable: $1995/server. It might be worth a look if you need a web-based OLAP tool.

Here is their feature list:

IE browser-based SSAS client
High-fidelity AJAX user interaction
Interactive table and chart views
Annotated public and private bookmarks
View and edit generated MDX
Sharepoint integration
Excel, Word, and PowerPoint generation
Publish static HTML site from bookmarks
Web tier caching
Search dimensions
Filter member sets
Sort and filter measures
Specialized business-centric and industry-specific views

Referencing a Custom .NET DLL from within a SQL Server 2005 SSIS script task

2007-08-02T12:57:00.001-07:00

It is not difficult to reference a custom .NET DLL from within an SSIS script, but there are a couple of things that you will need to do:

Sign your DLL
Put it in the GAC (copy it to c:\windows\assembly)
Put a second copy in the C:\Windows\Microsoft.NET\Framework\v2.0.xxxx folder

Once you have done all of these things, then it will show up in the list of available libraries when you attempt to add a reference to your script task. I found this out the hard way, so hopefully this will help at least one other person.

More details are available here.

Creating SQL 2005 Data Warehouse Metadata

2007-07-31T12:05:00.001-07:00

One of my favorite tools to use when designing a data warehouse is the Dimensional Modeling Spreadsheet created by Joy Mundy and Warren Thornthwaite of the Kimball Group in conjunction with their must-have book, The Microsoft Data Warehouse Toolkit. Not only does it allow you to fully build out your fact/dimension design, ETL logic, source-to-target mapping and more, it also generates a script that will create the first iteration of your data model once you have everything in place. I always try to fully flesh out this spreadsheet before I create the first SQL table, it really helps get things off to a good start. As an added bonus, Joy and Warren have a whole site full of useful tools and utilities for building a Data Warehouse.

Mark Garner has just released Beta 1 of a tool called Metashare. He appears to have based it on the Kimball Group spreadsheet I mentioned above, and pulls the same data back out of your data model for the purposes of documentation. I will look forward to checking out Metashare with the next DW that I build.

Microsoft SQL Server BI in Formula 1 - Case Study

2007-07-31T11:38:00.001-07:00

I love F1 racing. It the perfect showcase for technology, and I posted a while ago that Microsoft had been selected, along with McLaren to provide BI for F1. It looks like the first case study is now available as of a week ago. Here is a white paper and a video for you to look over.

According to the white paper, each Formula One car will typically generate 1–2 gigabytes of data in a race for 100 different sensors on the car. Each team runs two cars, and there will be 17 races next season. As far as I am concerned, this is one of the coolest applications of BI that I have heard of yet. Additionally, this is all being analyzed in real-time. I hope that we get some more case studies, but I'm not counting on anything.

How to configure SQL Server Integration Services (SSIS) 2005 for Load Balancing

2007-07-30T11:50:00.001-07:00

I should begin this post by mentioning that currently SQL Server 2005 Integration Services does not support Network Load Balancing (NLB) in the traditional sense. You can't just string together a series of SSIS servers, point them at a list of jobs and expect them to chew through them dependent upon available processing time and bandwidth.

Instead Microsoft recommends a "manual procedure" to perform SSIS load balancing. I am posting this for my own reference in the future, but it might come in handy to others searching for the same information. Here is a link to the info on MSDN.

SSIS Project Icon Broken in Vista?

2007-07-30T11:39:00.001-07:00

I just opened an SSIS project for the first time in Vista using SQL SP2 and noticed that my SSIS project used the same icon as my SSAS project. I opened an older project just to make sure, deleted, recreated, re-added, etc. and still incorrect icon. Not a big deal, but still an annoying little glitch. . .

Blog III

2007-07-30T11:28:00.001-07:00

This is my third blog. My first blog is at the official ASP.NET blog site, and I haven't posted there in almost a year. Also, I don't do as much straight .NET coding as I did years ago, so my posts wouldn't be very relevant in that forum.

My second blog was at a company that I worked for. I no longer work there, and they seem to have taken all of the blogs down as the company goes through a downsizing period.

This represents my third blog. I am planning to post interesting things related to the work that I currently do (mostly Data Warehousing and BI, and mostly Microsoft) to things that I think are cool. I am using Windows Live Writer for posting, and it is a slick (free) tool. I used Beta 1, and Beta 2 seems improved. I am just waiting on them to include native image uploading for Blogger, but will just use Picasa until then.