Team Foundation Service (TFS) Preview with Visual Studio 2008 (and BIDS)

It is now possible to use the Team Foundation Service Preview (Microsoft hosted TFS) with Visual Studio 2008 and BIDS (VS 2008 Shell).


1) Install Visual Studio Team System 2008 Team Explorer http://www.microsoft.com/en-us/download/details.aspx?id=16338.
2) Install Visual Studio 2008 SP1 even if it is has previously been installed http://www.microsoft.com/en-us/download/details.aspx?id=10986.
3) Install the Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview http://www.microsoft.com/en-us/download/details.aspx?id=29983.
4) Add your server (assuming you've already signed up for TFS preview) using the following URL: https://<yoursite>.visualstudio.com/defaultcollection.

If you receive an error stating:
TF31002 Unable to connect to this Team Foundation Server...
Possible reasons for this failure include:
-The Team Foundation Server name, port number, or protocol is incorrect.
-The Team Foundation Server is offline.
-Password is expired or incorrect.

MAKE SURE YOU ADD /defaultcollection to the end of your URL.

The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Ouch. I was recently working on a project that started with SQL Server 2008 R2 Standard Edition and eventually ended up with SQL Server 2012 Web Edition in a hosted environment. When I attempted to stand up a staging environment on the 2012 Web Edition instance I received the following error.

The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

I came to find out that the "Shared component library" feature of SSRS is only available in Standard Edition and up. I have to admit that before we moved from 2008 R2 to 2012 I reviewed the Features Supported by the Editions of SQL Server 2012 MSDN page and didn't realize that the Shared Dataset feature was a subset of the Shared component library feature. It appears that this feature is also sometimes referred to as the "Report Part Gallery" as can be seen on the Programming Features for Reporting Services Editions page, also on MSDN.

Publish SSDT Projects Without Visual Studio Using SqlPackage.exe

If you'd like to publish your SSDT database project but don't have access to your target database from the machine you run Visual Studio (with SSDT) and/or you don't have Visual Studio installed on your target server you can still publish your database by using SqlPackage.exe.

For those familiar with Visual Studio Database Projects (VSDB), SqlPackage.exe is the replacement for VSDBCMD.exe

Microsoft provides a WPI (web platform installer) for the Microsoft SQL Server 2012 Data-Tier Application Framework which lays down everything required to use the SqlPackage command line utility. You can also install the individual components from the Microsoft SQL Server 2012 Feature Pack if you prefer. They consist of the following:

1. dacframework.msi
2. SQLDOM.msi
3. SQLLS.msi
4. SQLSysClrTypes.msi

Once everything is installed you should be able to find SqlPackage.exe in a location similar to "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin."

Below is a basic sample of how to execute SqlPackage.exe.

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /a:Publish /tsn:TargetServerName /tdn:TargetDatabaseName /sf:c:\YourDacPac.dacpac

For an exhaustive list of available parameters see MSDN.

SSDT: unresolved reference to object [dbo].[sp_executesql].

Procedure: Some_Procedure has an unresolved reference to object [dbo].[sp_executesql].

Look familiar? There's a simple solution. We just need to add a reference to the master database.

1) Right-click the References folder in Solution Explorer.

2) Select the "master" System database.

3) Voila. The reference appears under the folder and the warning goes away.

Creating a CLR Function in SSDT

Creating a CLR function in SSDT (SQL Server Data Tools) is not very difficult but if you've never done it before you might be wondering where to start. Here goes...

Enabling CLR
First things first. While not directly related to SSDT it is necessary to enable CLR on your SQL Server instance since whatever code you end up writing won't be able to execute until this is done. All we have to do is set the clr enabled configuration to 1. See below.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

SSDT Project Properties
It is necessary to tell your SSDT project which version of the .NET Framework to use. If you're running SQL Server 2008 R2 you can use .NET Framework 3.5 or lower. SQL Server 2012 support .NET Framework 4.0. To get to the screen below, right click your project in the Solution Explorer and select Properties and then click the SQLCLR tab on the left-hand size. Set your Target framework appropriately.

Add a Function to the Project
Right-click within the project and select Add > New Item... 

Choose SQL CLR C# User Defined Function to add a function to the project.

Define the Function
Below is a sample function from MSDN. Obviously you can create a function to do whatever you're trying to accomplish.

Publish
Publish your SSDT project as you normally would.

SSDT makes database development and deployment a breeze but sometimes the approach can be less than intuitive. If you're used to creating CLR objects manually you might have been wondering how to create the assembly and the T-SQL reference to the CLR object's method. SSDT is kind enough to do this work for us.

If you look at Object Explorer you'll see the new assembly (DBProj) and the new dbo.validatePhone function.

If you further explore the dbo.validatePhone function you'll see that it is a wrapper for your CLR function.

Use it

Simple as that.

Conclusion
CLR objects can be as complex as the code that you write but SSDT makes their development and deployment easier. Hopefully this post shows you just how easy it is.

SSIS Event Handlers: Behavior In Package Hierarchy

I recently stumbled onto what seemed to be an odd performance problem in an SSIS project I was working on. The problem was that I had a number of straightforward packages that ran extremely fast but sometimes, without explanation, ran really slow.

Luckily the project included a robust logging system that provided, among other things, the execution duration of every package and Control Flow task.  The logs showed that the Data Flows in the packages in question were running in 10 to 20 seconds yet the packages were at times taking upwards of 20 minutes. Furthermore, I found that when called on their own, each package did not run much longer than its single data flow, yet when run as a child of another package (via an execute package task) the execution duration more than doubled. In one example I had a package that ran in 10 seconds on its own, 23 seconds as a child, 56 seconds as a child 3 levels deep, and almost 3 minutes when run as a child four levels deep. In that same example the Data Flow took only 7 seconds. So why was the package so slow when run as a child of one or more packages?

Time for SQL Server Profiler.

The logging mechanisms in place were stored procedures being called by Execute SQL tasks and were being passed things like a batch Id, Package Id, Source Name, Source GUID, Start Time, End Time, etc. Once I began profiling it was clear that there were way too many Pre and Post execute logging stored procedure calls.

Let me take a step back and explain the project/package design. The project contained about 50 SSIS packages, some of which were being called by Execute Package tasks within the project. For example, there was a Master package which called some staging packages and some staging packages that called lower level staging packages, and those in turn called some generic logging packages. There were as many as four packages in any given package execution hierarchy. In each package, regardless of where in the package hierarchy it fell, there were four event handlers:

1. OnError: Handled exceptions that were then logged to the log database.
2. OnVariableValueChanged: Handled the change in those variables we were interested in and logged to the log database.
3. OnPreExecute: Handled the logging of the pre-execution data (e.g. start time, source name, etc.) for all package tasks.
4. OnPostExecute: Handled the logging of post-execution data (e.g. end time) for all package tasks.

Getting back to what I was seeing in Profiler (many calls to the Pre and Post execute logging stored procedures), it only made sense that the OnPreExecute and OnPostExecute event handlers were being fired each time the logging stored procedures were being called. So why would those event handlers fire so many times?

I started to research event handlers propagation to understand what happens in SSIS when we have package level event handlers and a package design in which packages call other packages. I quickly ran into Jamie Thomson’s “SSIS: Understanding event propagation” blog post. In his post he talks about three things:

1. Events being caught by more than one event handler.
2. Event handlers firing events.
3. The System::Propagate variable.

Numbers 1 and 2 were what I was most interested in. Number 3 is something I’ve written about in the past in a blog post title “SSIS Foreach Loop Container: Continue on Error” when explaining how to continue a Foreach Loop container when a child task fails.

I took Jamie’s example and modified it to fit my situation which was a four package hierarchy with a single Control Flow task (empty script task just to get the event handler to fire) in the lowest level package and an OnPostExecute event handler in all packages. Instead of an empty Control Flow task in the parent packages, an Execute Package task was used to call the package one level down (e.g. Package_Level2 called Package_Level1, PacakageLevel3 called Package_Level2, etc.). Screen shots below depict the design.

Package_Level1 Control Flow

Package_Level1 OnPostExecute Event Handler

Package_Level2 Control Flow w/ Execute Package Edit Dialog

When running this sample package(s) I found that each subsequent level in the hierarchy more than doubles the number of events fired. When running Package_Level1 the OnPostExecute event handler is executed twice. Once for the Level1_EmptyControlFlowTask task and then again for the OnPostExecute of the package itself.  When running Package_Level2 the OnPostExecute event handler is fired a total of 8 times. See the table below. Package_Level1 bubbles up the Level1_EventHandler (twice), the Level1_EmptyControlFlowTask, and the Package_Level1 OnPostExecute events to the Package_Level2 event handler. In addition to those 4 events we still have the original two events we say when executing Package_Level1 on its own. And, we have the Package_Level2 OnPostExecute events for the Level2_ExecuteChildPackage (call to execute Pacakge_Level1 package) and the Package_Level2 package execution.

Sequence	Event Handler Package	Task	Event
1	Package_Level1	Level1_EmptyControlFlowTask	OnPostExecute
2	Package_Level2	Level1_EventHandler	OnPostExecute
3	Package_Level2	Level1_EmptyControlFlowTask	OnPostExecute
4	Package_Level1	Package_Level1	OnPostExecute
5	Package_Level2	Level1_EventHandler	OnPostExecute
6	Package_Level2	Package_Level1	OnPostExecute
7	Package_Level2	Level2_ExecuteChildPackage	OnPostExecute
8	Package_Level2	Package_Level2	OnPostExecute

When we execute Package_Level4 we see a total of 52 events fired, all triggered by the single Level1_EmptyControlFlowTask task in Package_Level1.

Below is the formula for determining how many additional events will be fired by the top-level package in a package hierarchy, per Control Flow task when there is a single OnPostExecute event handler task in all packages in the hierarchy.

y = 2^n + x

x = previous package level’s additional events

n = level in package hierarchy (starting at base package level 1)

y = total additional events fired at level n

Package Level	Calculation	Additional Events Fired	Total Events Fired
1	2^1 + 0	2	2
2	2^2 + 2	6	8
3	2^3 + 6	14	22
4	2^4 + 14	30	52
5	2^5 + 30	62	114

As you can see, having nested packages with package level event handlers can create quite a bit of overhead. I initially failed to see this in my logs because my logging stored procedures were ignoring the event handlers that were fired by other event handlers. However, the process still had the overhead of all the events firing and the stored procedure calls, the data just wasn’t being persisted to the database.

Event handler behavior can be very tricky. Don’t be like me, understand what’s happening before implementing your event handler logic.

Visual FoxPro Upsizing Wizard

If you're in the unfortunate position that I recently found myself in and need to move data from a Visual FoxPro (VFP for the die-hards) database to a SQL Server database there are a few gotchas you might run into including:

1. The lack of a .dbc file required by the Visual FoxPro Upsizing Wizard.
2. The error: The Upsizing Wizard could not set the SQL Server database to proper compatibility level for upsizing. In order to upsize, Visual FoxPro must set the compatibility level of the target SQL database to 6.5.

1. Creating a Visual FoxPro Database From Pre-existing .dbf Files
If you know anything about VFP then number 1 is probably a no brainer. When I started my project I knew absolutely nothing about VFP (and I still don't). So for those of you that are like me, it's very easy to create the .dbc file. Follow the steps below.

1. Open VFP
2. Click File -> New, select the Database radio button and then click the New File button.
3. When presented with the Save File dialog choose the location for your .dbc file.
4. Now click the Add Table button found in the Database Designer toolbox. 
5. When presented with the Open File dialog, navigate to the directory with your .dbf files and select all the files that you want to add to to your new database (.dbc file).
6. Done. Now you have a database that the Upsizing Wizard can consume.

2. Resolving the 6.5 Compatibility Level Error
Most versions of SQL Server allow you to set the compatibility level of each database within an instance. This allows SQL Server instances of a higher version to host databases of a lower version. For example, SQL Server 2012 allows you to host databases at compatibility level 90, 100, and 110. These numbers are just a confusing way to refer to SQL Server 2005, 2008 (2008 R2 doesn't have it's own compatibility level), and 2012. So what in pray tell is compatibility level 6.5 (65)? It actually refers to SQL Server 6.5 (the one before 7.0 which is the one before 2000). Yeah, it's old. 

Anyway, the VFP Upsizing Wizard requires that the SQL Server that you're upsizing (importing) to be able to support compatibility level 65 (version 6.5). If you're running into this error you're most likely running SQL Server 2008 or above as these versions no longer support compatibility level 65 or 70 and 2012 no longer supports 80 (version 2000).

What is one to do? What I found was the easiest route was to download SQL Server 2005 Express Edition. It's free, it's a small download (~55mb), and it supports 6.5. 

Once you've downloaded and installed SQL Server 2005 Express make sure you create your DSNs (the ones that you'll use in the Upsizing Wizard) using the SQL Server Native Client driver (not SQL Server Native Client 10.0 or 11.0 as these refer to 2008 and 2012).

When creating your DSNs keep in mind that if you're running a 64-bit operating system you must use the 32-bit ODBC Data Source Administrator. THIS DOES NOT MEAN TYPING ODBCAD32.EXE IN THE RUN PROMPT. In order to launch the appropriate administrator you must run it from a directory similar to C:\Windows\SysWOW64\odbcad32.exe.

At this point you should be all set to run the Upsizing Wizard. Good luck!