SSDT vs. VSDB

Microsoft strikes again by replacing a technology that was just beginning to gain a foothold with a product that has some nice improvements but does not replace all of the existing functionality. Here is a link to a long awaited feature comparison between the SQL Server Data Tools SQL Server database project (SSDT) and its predecessor Visual Studio Database project (VSDB).

Dimensional Modeling Skills Assessment

As everyone knows it's extremely hard to hire good people. It's equally as hard, if not harder to actually identify good people. I have previous posts with interview questions that dive into the details of T-SQL, SSRS, SSIS, SSAS, modeling, etc. Unfortunately many of these questions can be answered by those that have done a lot of reading and memorizing. It's my belief that the best technical interviews uncover a candidate's thought process, problem solving skills, and past experience. I recently put together a practical exam that attempts to do this. While I have only administered it once at this point I am confident that it is a good start to a practical skills assessment which I think should be part of any developer interview.

The assessment consists of business requirements, modeling requirements, an OLTP normalized, physical data model and some questions. The candidate is asked to review the requirements and data model and create a dimensional model that will serve as the basis for a data warehouse. There are also some follow-up questions.

The current version of the assessment asks for a dimensional model but has some questions that cross over into the physical world and assumes a SQL Server environment. It can probably be improved or at least made applicable to more organizations by making it more generic. However the position I created it for requires some knowledge of SQL Server so I incorporated it into the test.

Below is the problem statement and the physical OLTP data model. You can download the entire assessment here. Please e-mail me at david@elish.net if you'd like the answers.

You are a consultant and have been engaged by a manufacturing company to develop a business intelligence solution to help them reduce their raw material costs. Understanding the Purchase Order process which includes both purchases and receipts against those purchases is key to any potential cost reduction program.

The project’s sponsor has requested a first iteration of a Kimball-style dimensional model representing the material acquisition process. This model will be the basis of a star-schema based data warehouse.  It is your task to develop this model. 

SQL Server 2012 Editions

Microsoft has changed SQL Server licensing for the SQL Server 2012 release to better align with how customers are deploying applications and solutions. There will be Enterprise, Business Intelligence, and Standard editions. Below is directly from Microsoft's website.

Standard Edition still includes SSRS and SSAS but does not include the more self-service oriented BI features such as Power View (project Crescent) and Power Pivot for SharePoint Server. Data Quality Services and Master Data Services are also only available with Enterprise Edition.

The Excel Connection Manager is not supported in the 64-bit version of SSIS

When attempting to execute a package that makes use of an Excel Connection Manager you may be presented with the following errors.

• SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "YourConnectionMgrName" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
• [Connection manager "YourConnectionMgrName" Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

The problem is that Microsoft doesn't provide a 64-bit Excel Connect Manager component (as stated in the error). This means that to run the package it must be run using the 32-bit DTEXEC.exe. When running in BIDS (Visual Studio) it's as simple as setting a project property. Below is the default property on a 64-bit machine. Change True to False to run the package.

If you're running the package under a SQL Server Agent job you must also set a similar property on the job step that runs the package. Notice the Use 32 bit runtime property. Make sure this is checked.

Overwrite Excel File in SSIS: Workaround for Excel Connection Manager

I recently needed to create a process that would ultimately create an Excel file for import into another system. The other system was built and maintained by a third party and I had no control over it. It required that an Excel file be present in a specific directory with a specified name. The solution required that 1) if the file did not already exist it would be created and 2) if the file did already exist that it would be overwritten and a copy of the new file would be written to an archive folder with the date and time appended to the file name (so that the other historical files wouldn't be overwritten and so that it would be easy to know what date and time the file was created).

Current File Name: Extract.xls

Historical File Name: Extract_YYYYMMDD_MMSS.xls

I figured this would be easy. The first thing I did was look for the OverwriteFile or ReplaceFileproperty. What I found was that the only non-connection specific property the Excel Connection Manager exposed was FirstRowHasColumnName. At that point I figured I would experiment and find out what would happen if I tried to write to a pre-existing file. The result was that the data being written to the file was simply appended to the pre-existing data in the file. Definitely not the behavior I was looking for.

I then hit "the Google" as George W. so fondly calls it. I found solutions that proposed deleting data from worksheet(s), deleting worksheets, and creating new worksheets using the Jet 4.0 SQL syntax to "DROP" and "CREATE" tables. In Jet/Excel terms, a table is an Excel worksheet. I found that the DROP didn't actually delete the worksheet but deleted rows from the worksheet. This could have worked except that the next time the worksheet is written to, the new rows start after the last row of the previously deleted data, leaving a bunch of empty rows in the beginning of the worksheet. This approach did not cut the mustard. So ended my search for the wheel I was attempting to not reinvent.

The approach I came up with does the following.

1. Creates a shell (only the headers) Excel file with the name Extract_wip.xls.
2. Uses a Data Flow to populate the wip Excel file.
3. Copies the wip file to an Archive folder but renames to Extract_YYYYMMDD_MMSS.xls.
4. Renames the wip file to Extract.xls, overwriting the previously created Extract.xls files (if one exists).

This is a screen shot of the Control Flow.

*If you don't have an archival/historical requirement you can remove that functionality and make this solution even simpler.

Connection Managers

ExcelFile

The Connection Manager used in the final step of the process to rename the wip file to the final file name. Nothing fancy here although I would suggest making this configurable.

ExcelFileArchive

The Connection Manager used to copy the wip file to an archive folder with the date and time in the file name. Below is the expression used to generate the file name. You will want to set the Connection Manager's ConnectionString expression to this.

"C:\\ExcelExtract\\Archive\\Extract_" + RIGHT( "0000" + (DT_STR, 4 , 1252) DATEPART( "year" , GETDATE() ) , 4 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "month" , GETDATE() ) , 2 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "day" , GETDATE() ) , 2 ) + "_" + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "hour" , GETDATE() ) , 2 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "minute" , GETDATE() ) , 2 ) + ".xls"

ExcelFileForDataFlow

This Connection Manager should point to the wip file. You'll want to have a ConnectionString expression similar to the following.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelExtract\Extract_wip.xls;Extended Properties=\"EXCEL 8.0;HDR=YES\";"

*HDR is whether the file has a header row.

ExcelFileWIP

This Connection Manager points to the wip file and is used to copy and rename the populated Excel file.

SomeOLEDBDataSource

This is used by the data flow. There could be one or more Connection Managers for your data flow.

Control Flow Tasks

Connection Type: EXCEL

Connection: ExcelFileForDataFlow

SQL Statement:

CREATE TABLE [ThisWillBeYourWorksheetName](

[SomeCharVal1] nvarchar(25),

[SomeCharVal2] nvarchar(255),

[SomeCharVal3] nvarchar(50),

[SomeDecimalVal1] double precision,

[SomeDecimalVal2] double precision,

[SomeIntegerVal1] integer)

*It is important that you use the JET syntax including the appropriate data types when writing your CREATE TABLE script.

This task will create a new Excel file with a worksheet named using the name in your CREATE TABLE script. It will use the file name found in the ExcelFileForDataFlow Connection Manager.

*If a file by the same name exists it will add a worksheet to that file. This is why we want to make sure this file does not exist when we start our process. Hence the wip file.

This is the Data Flow that writes to your Excel File. It doesn't matter what the data flow does, just make sure that it writes to an Excel Destination that uses the ExcelFileForDataFlow Connection Manager.

Destination Connection: ExcelFileForArchive

OverwriteDestination: True

Operation: Copy File

Source Connection: ExcelFileWIP

This File System Task copies the wip file to an Archive (or any other) folder using the Extract_YYYYMMDD_MMSS.xls naming convention.

Destination Connection: ExcelFile

OverwriteDestination: True

Operation: Rename File

Source Connection: ExcelFileWIP

This File System Task renames the wip file to the Extract.xls name, effectively overwriting the previously created Extract.xls file (if one exists).

Summary

While it seems like it would make sense for the Excel Connection Manager to allow for the overwriting of an existing file, it doesn't. The above process is the simplest way I have found to reproduce this seemingly lacking functionality.

SQL Server Default Backup Location

SQL Server makes it easy to set a server's default data and log locations. In SQL Server Management Studio, simply right click on the top level server node in the Object Explorer and select Properties. Then click on the Database Settings page selector on the left side of the screen. At the bottom of this screen there are text boxes for entering the default Data and Log locations.

Unfortunately it's not as easy to set the default backup location. To do this you need to modify the server's registry. Open the Registry Editor by typing regedit either on a command line or in the Search programs or files box at the bottom of the Windows Start Menu. Once you're in the Registry Editor navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YourInstanceName\MSSQLServer. Now enter the directory you'd like to be your default. Save and close the Registry Editor.

The next time you attempt to backup or restore a database you will be presented with your new default backup location. No more navigating to a different location over and over again.

The Future of Visual Studio Database Projects

It is with great sadness that I relay the decision by Microsoft to do away with the Visual Studio Database Project type that I love so much. This project type has given database developers the tools needed to implement high quality Database Development Life Cycle processes. Some highlights can be found here.

However, all is not lost. VSDB will be replaced by what was until yesterday referred to as project Juneau and is now officially named SQL Server Data Tools. It will also be hosted in the Visual Studio environment.

SSDT has many similarities with VSDB but is a complete rewrite from what I understand. Below are some of the features previously unavailable in VSDB.

1. Code navigation (Go to Definition, Find All References)
2. Enhanced code analysis and design time validation
3. Table designer similar to SSMS
4. Connected (live database) and disconnected (declarative) development
5. LocalDB - SQLExpress without the need to explicitly install any SQL Server instances
6. Entity Framework integration for a much more tightly integrated development experience between the data and application layers
7. Support for CLR objects
8. Support for SQL Server 2012 and SQL Azure databases (both support 2005, 2008, and 2008 R2)
9. Integration of SSIS, SSRS, and SSAS projects into the next edition of Visual Studio (currently Visual Studio 2011)

In my opinion, of the biggest shortcomings (as of SSDT CTP3) of SSDT is that there is no longer support for database unit tests. Automated unit tests have a huge impact of quality and I believe are key to robust DDLC.

While I am sad to see VSDB go, I am confident that Microsoft will create an even better tool in SSDT. I look forward to exploring it and sharing my findings in future posts.