I suspect that most of us who have been using SQL Server 2005/2008 SSIS for any length of time know what a great resource SQLIS is (if you don't know, check it out - www.sqlis.com). This site provides downloads of several custom SSIS components that are now integral parts of any Pcon SSIS project.

One of these can't-live-without components is the Row Number transform. This component, like my other must-haves, was developed by Konesans (www.konesans.com).

When I first started using SSIS, I used a Script transform, as described in Donald Farmer's The Rational Guide to Extending SSIS 2005 with Script, to assign surrogate keys to data mart dimensions and facts in the data pipeline. Although I was grateful to have this model at the time, I don't really like to use script components if I can avoid them. I've run into too many compile issues, especially with 64-bit environments. The Row Number transform delivers a much simpler way of assigning surrogate keys (simpler because I don't have to write any code…always a plus for me!).

The Row Number transform…numbers rows. Yeah, sounds obvious, right? The transform UI allows you to assign a seed value, an increment value, a target pipeline column to store the sequential numbers and presents an option to store the final number in a target variable:

 

 

The Number Column can be a pipeline column that already exists (I always create the surrogate_key column in a Derived Column transform), or you can choose to let the Row Number transform create a new column for you. 

In my standard table processing methodology, I retrieve the next surrogate key value for the table prior to launching the data flow task and store it in a variable.  I then use SSIS Expressions to assign this seed value to the Seed property of the Row Number transform.

As with all data flow transforms, you set properties for the Row Number transform at the data flow task grain.  Just click anywhere on the Data Flow design surface to be able to access the properties for the transform:

Since I haven’t found a way yet to insert an expression without popping open the Property Expression Editor window (by clicking the ellipsis next to Expressions – but you knew that already…), I open the editor and locate the Seed property for the Row Number transform (I’ve named it rnAssignSurrogateKeys in the screen shots):

There’s an issue with the way the custom transform interacts with the Property Expression Editor that sometimes results in an error when you try to directly assign the Seed property to a variable – regardless of the data type of your variable, the editor chokes and thinks you’re trying to assign a decimal value to a property that only accepts integers.  On the SQLIS page for the Row Number transform, the Troubleshooting section describes this as a known issue, and shows how to manually edit the .dtsx code to fix the issue.  Since I don’t want to mess with the code directly (no coding for me – see above), I prefer to fool the editor.  By assigning a zero to the Seed property as shown, you can successfully exit the editor.  Then, in the Properties pane, you can change the expression value to your variable as shown in the Properties screen shot above.

This only appears to be an issue the first time you try to assign a value to the property.  Once you’ve changed the value to a variable in the Properties pane, you can open and close the editor with no further errors – it will present the variable as the expression value:

Downstream of the Row Number transform, you can then assign the output column to the surrogate key column for your table.  Piece of cake.

When you find yourself in a situation where you would like to concatenate the data items for a data object into one line, where the object can be related to many data items that are stored in a separate table, here is a quick way to accomplish that in SQL without expressly using a loop:

• Lines 2-3: declare and initialize variable that will be used to hold the concatenated string
• Line 5: select variable to equal itself plus the string you are interested in and a delimiter such as a comma, if desired
• Lines 6-9: the query that returns the data items for the object you are interested in
• Line 11: trim off the excess delimiter from the concatenated string

The resulting concatenated string:

An example application is to place this type of concatenation in a function where you can submit the ID for the object you are concatenating data items for to the function, and have it return the concatenated string.

Part 2 – Conversion

 

In part one of this post, I discussed preparing your Virtual PC 2007 VHD files for transfer to a Windows 7 box, and setting up the Windows 7 machine to use Windows Virtual PC, Microsoft’s Windows 7 virtualization engine.  In this post, I’ll step through the process I went through to convert a legacy VHD file.

 

This post refers to a Virtual PC 2007 VHD with Windows XP SP3 as its guest OS.

• Launch Virtual PC. This new version presents as a Windows Explorer window rather than the VPC console as in VPC 2004 and 2007. You'll see an option to Create Virtual Machine:

 

• Choose the Create virtual machine option. The steps are very similar to the old VPC wizard - first you'll create the configuration file (which now has a .vmcx extension rather than .vmc), specify memory and networking options, then identify the VHD.

 

Since you're converting an existing VHD, choose the Use an existing virtual hard disk option and navigate to its location.

• Once you've completed this step, the .vmcx file will appear in the Virtual PC explorer window:

• To open the VPC, double-click on the .vmcx file. The first time you open the VPC, it will appear to be detecting new hardware. It may or may not require your interaction to complete the hardware detection, but if it does, it probably will not be able to find the driver (since it can't identify the hardware device in the first place). This does not appear to adversely affect anything.

 

• When you're opening the VPC, it will tell you that it's restricting the cursor to the inside of the VPC window and give you the key sequence to release it to the host machine (<Ctrl><Alt><Left Arrow>). Unfortunately, this combination of keys may not work for your hardware.  On my laptop, for instance, this key combination rotates the host screen. So for the moment at least, you're stuck with the restricted cursor.

 

• After the VPC has done its hardware detection dance, you'll want to restart it. If your VHD login was not previously password protected, you'll want to create a password for the login account now. After doing so, restart the VPC.

 

• While the VPC is booting up (i.e., before the cursor restriction kicks in), from the Tools menu, select Install Integration Components. This is the Windows 7 version of Virtual Machine Additions.

You'll get the following message:

Click Continue.

• Once the VPC is open, it will present the Integration Components setup.exe in the CD drive:

Run setup.exe. It will appear to detect more new hardware, but this time you shouldn't be required to intervene.

• Once the Integration Components have installed, the last screen will prompt you to download the updates required for running XP in this new mode. I usually let it navigate to the download URL as it wants to do, and run the hotfix update at this point. After the hotfix is installed, let the VPC restart.  This is the update URL, in case you miss this step:

http://www.microsoft.com/downloads/details.aspx?FamilyID=e5433d88-685f-4036-b435-570ff53598cd&displaylang=en

• Even though you've installed the Integration Components, you still have to enable them. This appears to be user specific, although I haven't run enough tests to be absolutely sure of it. Unless you enable the features, you won't be able to copy and paste between the VPC and the host machine, nor share drives.

• You may be prompted for a login when you choose to Enable Integration Features. I haven't been able to determine consistently when this shows up - definitely when the machine is attached to a domain, possibly when there's more than one non-system user account.

 

• I’ve run into some oddities with the way a couple of VPN client tools interact with the Integration Features auto-enable (specifically the Cisco VPN client and the Cisco Any Connect web client).  When Integration Features are set to auto-enable at startup, the VPN client chokes with a user authentication failure before the user login screen is even presented.  To work around this issue, I set the Integration Features to enable manually, by clearing the Enable at startup check box as shown below.  When I do this, both of the Cisco VPN clients can connect successfully.  For the Windows client, I was able to enable the Integration Features after startup and still connect successfully.  For the Any Connect web client, enabling the Integration Features blocked the connection (the application delivered a “not supported” error message.)  I mention this as a heads-up:  you may need to play with this particular setting a bit if you experience odd connectivity behavior from within your VPC.

 

Part 1 - Preparation

 

Our extended firm is in the process of moving from the Vista operating system to Windows 7. As the first one in the consulting group to take the plunge, I had the privilege of testing the conversion process for some of our core applications.

At Perkins Consulting, we rely heavily on virtualization to manage the connection environments for our various clients. Consequently, one of the primary areas of concern for us in the conversion process was to make sure our virtual machines would still function under the new operating system.

Will they? Well, yes. More or less.

For those of you familiar with the Microsoft Virtual PC application, you'll remember that there wasn't a whole lot of difference between Virtual PC 2004 and Virtual PC 2007, at least cosmetically. The same is not true for the differences between Virtual PC 2007 and Windows Virtual PC (the Windows 7 virtualization engine). Virtual PC 2007 will not run on a Windows 7 machine. If you want to run a virtual PC on a Windows 7 box, you have to use Windows Virtual PC for Windows 7. And if you want to use your 2007 VHD files, you have to prepare and convert them for use in Windows Virtual PC.

As the official Conversion Sacrifice for PCon, I got to figure out how to make it work for us. In this two-part post, I'll describe the lessons I learned from the process. In the first part (the one you're reading now), I'll discuss preparing your legacy VHD files for conversion and setting up the Virtual PC application on the Windows 7 box. In the second part, I'll step through the conversion process for a 2007 VHD running Windows XP SP 3 as the guest OS.

 

Prepare Your 2007 VHD Files for Transfer

On your machine running VPC 2007, for each Windows XP SP3 VHD you wish to transfer:

• Remove the Virtual Machine Additions from the VPC. If the additions are up to date, when you select the Install or Update Virtual Machine Additions from the Action menu, the only option presented to you will be Remove. If you get an Install or Update option, you'll need to apply the updates and restart the VPC before you can get the Remove option.

If you fail to complete this step, you will most likely get a drive read error when you try to open the VPC with the Windows 7 version of Virtual PC.

• Windows 7 Virtual PC will only allow the following guest operating systems:
  • Windows 7
  • Vista
  • Windows XP SP3

If the VHD you wish to convert does not have a supported OS, you may not be able to successfully convert it for use with Windows 7.  I’ve seen some blog posts that others have been able to convert a VHD with an OS outside this list – Server 2003, for instance – but these are the only officially supported guest operating systems.

 

Prepare Your Windows 7 Box for Windows Virtual PC

• Windows Virtual PC will only run on a machine with hardware virtualization capabilities.  Before installing Windows Virtual PC on your Windows 7 box, enable hardware virtualization in the BIOS.  This is a critical step, according to the download notes.  If you fail to make the changes to the BIOS, untold death and destruction may ensue.  (Or at the very least, the application won’t run.)

 

• As of the date of this post, Windows Virtual PC is a release candidate.  Download the RC bits at the following URL:  http://www.microsoft.com/windows/virtual-pc/download.aspx

 

• Install the Windows Virtual PC on your Windows 7 machine.

 

• In addition to the Windows 7 Virtual PC bits, the above site also has a download for Windows XP Mode.  You may download and install this if you wish, but its use is outside the scope of this post.

"You can't get there from here."

Part 2

In Part 1 of my post on this topic, I described how to configure a package containing UNC paths on flat file destinations to execute successfully in BIDS debug mode.

At the risk of being repetitive, here's the system background:

• The SQL Server data mart resides on a remote server running 64-bit SQL Server 2005 Enterprise Edition.
• Access to the file system on the server is not allowed.
• SSIS development occurs on a virtual server with only the SQL Server client tools installed.
• The destination for the output files is on yet a third machine, only accessible to the server and the development machine via UNC paths.

Now that I had successfully executed the package in debug mode by forcing BIDS to use the 32-bit runtime to recognize the UNC paths, I was ready to try to schedule the package via a SQL Agent job.

I'm used to using the CmdExec step type in SQL Agent to force execution of a package in 32-bit mode (I have to do this any time the package includes Excel file sources, for instance). However, when I used the usual method for this particular package, I got yet another error message:

Error: 2009-08-31 16:01:55.99 Code: 0xC001401E Source: stage_reports Connection manager "Extract File Cards" Description: The file name "\\target1\Dest-Data\Cards.csv" specified in the connection was not valid.

Well, rats.

Since I wasn't able to access the file system or view the context of the server in the network, I had to find a way to work around this - i.e., schedule the package in 32-bit mode without using SQL Agent.

To do this, I wrote a batch file to execute the package using 32-bit dtexec from the context of the development machine (I used dtexecui to get the command line for the package):

"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "\\vm-dev1\ReportStaging\SSIS\report_staging\stage_reports.dtsx"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

By scheduling the batch file as a Windows task and bypassing SQL Agent entirely, I was finally able to get the UNC paths to work on the calling machine, the server and the destination machine.

Whew!

"You can't get there from here."

Part 1

As part of a recent client project, I had to use SSIS to create six pipe-delimited flat files for consumption by a third-party report writer. I created a template container with all the tasks necessary for the conversion. The template included variables to hold the path and file name for the destination files, and I used an expression in the connection string for each destination Connection Manager to reference the variables.

System background:

• The SQL Server data mart resides on a remote server.
• Access to the file system on the server is not allowed.
• I do SSIS development on a virtual server with only the client tools installed.

During testing, I used a local drive path on the development machine (E:\data) and the package executed in debug mode with no problem. The final destination for the files was on a third machine, so I had to use UNC paths for access. After I changed the variables to UNC paths, I got the following error when executing in debug mode:

Error: 2009-08-31 15:20:49.96 Code: 0xC020200E Source: <task name> <destination adapter> [346] Description: Cannot open the datafile

I could open the Connection Manager for the files defined by UNC pathnames inside of the BIDS designer, but the execution of the package failed because it couldn't open the file.

Hmmmm. Very suspicious.

I checked the SQL Server version on the remote server. Sure enough, it was the 64-bit version. I set the Run64BitRuntime property for the project to False and the package executed successfully from the BIDS designer.

Looks like UNC paths on flat files are another point of conflict between 64-bit and 32-bit SQL Server. Since the BIDS designer is a 32-bit application, I could open the files from inside the designer. Executing the package, however, invokes the 64-bit executable, unless BIDS is forced to use the 32-bit executable with the above setting.

Of course, then I had to try to schedule the thing….(to be continued)

Often as consultants we advise our clients that the best way to get data quality is by ensuring that the people who are closest to a transaction be the ones that enter the data about that transaction. It sounds trite, but we see many cases where financial department staff are trying to fill in the missing details that should have been captured by the sales force that originally put the transaction together.

Here at Perkins Consulting we aren’t a transactional organization – we’re a services organization. So we want the people who are delivering our services to have an opportunity to put the truth that they experience out there for the benefit of our clients, our partners and the world. Hence, a blog with content created by our consultants!

We hope that this blog provides highly focused and relevant information for our clients. If not, I’d like to hear suggestions on how we can make it more focused and relevant.We also want to show some of the work that we’re proud of and some of the knowledge that we’ve put together in the 15 years that Perkins Consulting has been around.