Useful Tools

More SSIS posts are on tap.... got a few in draft mode... including some more issues with importing Excel, and some patterns for single file imports.

In the meantime, a useful tool I've been using to compare SQL Server databases - usually for rolling out changes from development to production.

DBDiff

Select two databases, click compare, and a list by object type will show any database objects that are on one side and not the other, or are different between the two.

Previously, I had used a tool called Database Compare from StarInix - It's free (cost), but not open source, so I started looking on CodePlex for an open source project that seemed like it could grow as SQL Server versions changed, etc. DBDiff is not very active either, so I'm keeping my eye on alternatives every once in a while.

Both tools offer alter scripts to bring one side in sync with another.

InfoPath in SSIS

Importing XML files generated by InfoPath has some nuances:

The article SSIS: Using InfoPath XML Files in SSIS XMLSource Adapter at SQLJunkies.com by 'ashvinis' which covers them pretty well.

Basically, to make the XML file available in such a way it can be mapped to a relational source of some type, XML Tasks are needed to:
1. Remove extra namespaces

2. Wrap the InfoPath fields in a Table/Fields Schema

(The article points out that a 'default' InfoPath form will only contain a 'Fields' element. i.e.

<?xml version="1.0"
encoding="UTF-8"?>
<?mso-infoPathSolution solutionVersion="1.0.0.2"
productVersion="11.0.6357"
PIVersion="1.0.0.0"
href="file:///c:\infofile.xsn"
name="urn:schemas-microsoft-com:office:infopath:Info1:-myXSD-2005-04-27T19-26-55"
?>
<?mso-application
progid="InfoPath.Document"?>
<my:myFields
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-04-27T19:26:55"
>
<my:FirstName>Wenyang</my:FirstName>
<my:LastName>Hu</my:LastName>
<my:PhoneNumber>425-123-4567</my:PhoneNumber>
</my:myFields>

I've written a small XSLT file, that will wrap this default 'myFields' element in a 'myTable' element:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" encoding="utf-8"/>
<xsl:template match="*/comment()processing-instruction()">
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
<xsl:template match="myFields">
<myTable>
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</myTable>
</xsl:template>
</xsl:stylesheet>

This can be layered with the steps in the SQLJunkies article to provide a fully automated prep of InfoPath XML, which can then be used in imports to relational databases such as SQL Server.

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.

Learning DotNet

As an experienced developer, colleagues looking to get into the field often ask me how I "learned to program". I did take formal classes at junior college and university, and to some extent my experience was sort of vaguely finding my path into jobs that required me to pick up Access, and later Visual Basic, FoxPro, and start using it.

The other area that really filled in the gaps between theory and trial and error for me was an online education site called 'ZDU'. The site isn't around anymore, or at least not in the form it was then, but one of the authors/instructors, John Smiley, that taught in that format now accompanies his own books with internet classes through his website, http://www.johnsmiley.com.

I learned from him back on VB 5, then VB 6 (I still have a number of his spiral bound ZDU 'workbooks' on the shelf) Reading the abstracts of his current books, of course updated for .net 2.0, 3.0, etc. and expanded to VB.net and C#, it appears to follow a similar format that I highly recommend. Mr. Smiley takes you from the beginning of a fictional project, that gets built out chapter-by-chapter to a finished product at the end. I really like the format as it tends to follow how I develop applications in the real world. I highly recommend his books, and his internet classes, to someone looking to learn to program and wants to focus on the Microsoft Dot Net product.

Take a look at his classes or books