SQL Server Express Edition

Found a good article on MSDN - comparing the various downloads of SQL Server Express, entitled 'What's up with SQL Server 2008 Express editions'.

I just completed setting up a VPC with SQL Server 2008 Express as a demo environment for an article I'm drafting. The 2008 version, at least the W/ Advanced Services is a 512 MB download (SQL Server 2005 Express Edition, w Advanced Services was 234 MB) I haven't been able to find a source on this, but it appears that the 2008 version ships with the entire codebase to install any edition of SQL Server. The product key you enter then limits what edition (Express, Standard, Enterprise, etc.) is available to you.

Take note of the prequisites:

• .NET Framework 3.5 SP1
• Windows Installer 4.5
• Windows PowerShell 1.0

The install will not run without the first two, but you'll only find that out after the installation files are extracted (which was a bit sluggish on my VPC), and then both required a reboot. The install will run and then warn of PowerShell missing, which can be enabled without a reboot, then the install of SQL Server can continue.

New Open Source Project - File Server Documenter

A new open source project has been started at CodePlex for the File Server Documenter.
From the project's home page:

File Server Documenter contains routines and a basic interface to scan a file server and produce raw documentation. Oriented towards a customer/technical support person rather than the server administrator. It can be considered an auditing tool, or just providing general information about what the file server is being used for.

The first release is really some very tactical tasks I've had over the past few years - starting with a single file console app that scanned a file server for Access databases (the original exe was called MDBDocumenter, in fact)

A great tool for mounting Disk Image (*.iso) files

A great tool for mounting Disk Image (*.iso) files so they can be accessed through the Windows file system is Virtual Clone Drive from SlySoft. While not specifically related to Virtual PC, I’ve found that as I’ve used more virtualization, it’s more common to store or move software as
ISO’s vs. ever burning to a physical CD or DVD. In a situation when you need to access those on a host (non-Virtual PC) computer, the traditional way was to burn the ISO to a CD, then run software from a CD. Virtual Clone Drive allows the ISO to be mounted.

The console is very minimal, allowing you to create up to 8 ‘drives’. Each of these shows up as a new drive letter in Windows.

On each new drive letter, simply right-click:

Choose Virtual Clone Drive ->‘Mount…’ and browse to your ISO

Plus, the drive letter has a wacky sheep icon as a conversation starter… what’s not to love?

SQL Server Integration Services – Beyond the Wizard – Part 1

In a method not entirely unusual for me, and I'm sure many of you, I'm picking up a technology 'on-the-fly'. SQL Server Integration Services (SSIS) 2005. Since I have a fairly solid understanding of Extract-Transform-Load and other integration topics, and experience with the predecessor tool - Data Transformation Services (DTS), most of the learning curve has been the nuances of the tool.

I'm finding I'm learning it in essentially a 'refactoring' approach. I've been able to get done the work I need to, but not always in the most elegant way.

This blog series will highlight some topics that will be of interest to the intermediate developer - You've been able to build a basic data flow, import/export, or other common routine, possibly only what the import/export wizard out of SQL Server Management Studio generates. Perhaps a number of them following a similar pattern. The next task is to polish it off to make it more dynamic and maintainable on a long-term basis.

One of the first issues I noticed with my SSIS project was that the connection strings were stored in each package - This is what you will get if the results of the Import/Export Wizard are saved as an SSIS package. This export shows a routine import of a CSV file into an AdventureWorks database on the local SQL Server.




I didn't see an obvious way to move the 'Connection Managers' from the package to the project level, the actual connection string property of a Connection Manager seemed like the next bet, but couldn't find a way to dynamic-ize this. While I had seen the Data Sources container under the project - I couldn't figure out how to reference these inside a package.

To change an SSIS package from using a per-package 'Connection Manager' to using a per-project 'Data Source' (useful if many SSIS packages use the same database) involves:

• Creating a new project-level data source


• Associating each Control Flow and Data Flow Task that uses the package's Connection Manager with the package source


• Deleting the package-level connection manager
  

1. Create a Project-Level Data Source

a. Right-Click the Data Sources folder under the project

b. Choose 'New Data Source'

c. Click 'Next ->'

d. Select the appropriate Server, Database, and Authentication

e. Choose 'OK'

2. Associate each Control Flow and Data Flow Task that uses the Package's Connection Manager

a. Where it now shows 'DestinationConnectionOLEDB' (the default name)

b. Choose the drop-down -> New Connection…

c. Select the data source created in Step 1 above

d. Repeat for any task objects that reference 'DestinationConnectionOLEDB'

3. Delete the Connection Manager

To tidy things up, from the connections managers tab, delete 'DestinationConnectionOLEDB'

Summary

These steps demonstrated converting from a per-package connection manager to a data source. The Data Source can now be referenced in other packages, providing a single home for the Server Name, Database Name, and Authentication.

In this case, only the target data source - AdventureWorks was created. The source file could have been created as a data source as well, but there is less value if one side or the other of the integration is relatively unique to that package. Where I was doing multiple connections between the same databases, it would make sense to create both as Data Sources.