SSIS: Case sensitivity may expose issues with change deployment

Every major software release of a large scale enterprise product comes with database changes. Development teams who have been around for a while are used to a manual (a.k.a. “legacy”) way of implementing change management and deployment. As tools evolve, teams start using source control for change tracking and management. The weak link in this transformation remains change deployment, which unfortunately remains manual in most cases. This link often fails when newer technologies like SSIS are used for data migrations, cleanup and ETL operations.

When working with SSIS, VS_NEEDSNEWMETADATA errors typically indicate that schema changes were made after the packages were developed. SSIS is case sensitive. On case-insensitive databases, random VS_NEEDSNEWMETADATA errors may also indicate issues in your database change management and deployment process.

This article is based on a true incident that I ran into a couple of months ago.

The system was an on-premise, case insensitive system akin to AdventureWorks. Visual Studio Team Foundation Server (TFS) was used for source control with database projects for database development. This allowed the team to create automated deployment scripts for new databases that are prepared for new customers. For existing customers, the team manually developed a change script so that they could handle extensions that DBAs may have made to the system for reporting and HA/DR purposes. I will therefore be explaining the issue by using the AdventureWorks sample database as reference.

The Change That Started It All

Assume that a business request is made wherein we are now required to track whether a particular sales order was shipped as gift or not. This flag should also make it’s way to the data warehouse to allow businesses to decide their strategies for gift orders.

In the OLTP system, the most common and obvious way to implement this is by introducing a flag to the [Sales].[SalesOrderHeader] table. When we added this change to source control, it was added with the proper casing standard that we used throughout the project.

The screenshot below shows the changes made to the table definition.

The process to transfer data into the data warehouse uses a staging database, which for the most part has a schema that’s identical to the OLTP in the Extract part of the ETL process. For the purposes of this demo, we will be using a table in the tempdb ([dbo].[SalesOrderHeader]) to substitute the staging database.

Now, assume that we have a SSIS package that extracts data from the [AdventureWorks2012].[Sales].[SalesOrderHeader] and pumps it into the staging [tempdb].[dbo].[SalesOrderHeader] table using a direct 1:1 column mapping for the sake of brevity. The SSIS package is therefore similar to the following:

Observe the case of the new column [IsOrderAGift] in the screenshots above.

Change Deployment Script

On a new database created using the database deployment scripts generated via TFS, the package obviously works fine.

However, a deployment package/script needs to be developed so that pre-existing systems can be upgraded with the latest changes. The following manual change deployment script was therefore created to deploy the addition of the column [IsOrderAGift] to the table [Sales].[SalesOrderHeader].

/*
    Script used as part of demonstrating that VS_NEEDSNEWMETADATA errors may be because of issues with change management & deployment
    - Nakul Vachhrajani (@sqltwins)
    - http://nakulvachhrajani.com
*/IF OBJECT_ID('[Sales].[SalesOrderHeader]','U') IS NOT NULL
   AND NOT EXISTS (SELECT *
                   FROM INFORMATION_SCHEMA.COLUMNS AS isc
                   WHERE isc.TABLE_SCHEMA = 'Sales'
                     AND isc.TABLE_NAME = 'SalesOrderHeader'
                     AND isc.COLUMN_NAME = 'IsOrderagift'
                  )
BEGIN
    ALTER TABLE [Sales].[SalesOrderHeader]
        ADD [IsOrderagift] BIT NOT NULL CONSTRAINT [DF_SalesOrderHeader_IsOrderAGift] DEFAULT ((0)) WITH VALUES;
END
GO

Notice that:

The case of the column name is different (Expected: [IsOrderAGift], Actual: [IsOrderagift])
Because the database is case in-sensitive, this will not have any issues with the execution of the application. The difference in case will therefore remain unnoticed

The Problem

Now, let’s deploy this change to a backup of the AdventureWorks2012 database. After successful deployment, let us try to run the ETL package again.

As can be seen from the screenshots above, we ran into the following error:

[SSIS.Pipeline] Error: "Extract data from AdventureWorks2012 (Sales -> SalesOrderHeader)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

On databases where the column was created with the expected case ([IsOrderAGift]), we saw that the SSIS package ran just fine. However, the package failed when the case on the column name was different, even though the database itself is case in-sensitive.

This is because SSIS is case sensitive.

SSIS is ultimately .NET code and not T-SQL. This .NET code is case sensitive by default. Hence, when the SSIS package attempted to validate the external meta-data of the OLEDB source and destination components, it encountered a column with a different name ([IsOrderagift] is different from [IsOrderAGift] when performing a case sensitive comparison), resulting in a VS_NEEDSNEWMETADATA error.

Fixing the column name in turn resolves the issue, teaching us a couple of important lessons with respect to change deployment.

Lessons Learnt

If changes to production systems are being rolled out manually versus the automated deployment of “new” schemas, the following best practices should be followed with respect to certification of the change deployment as part of QA:

Once the change scripts are deployed, always run a case sensitive schema comparison between a newly-built schema v/s the upgraded schema
Always test the application, including all peripheral and interfacing utilities with both newly-built and initialized schemas as well as schemas upgraded via manual deployment
Change deployment scripts should be subject to the same code review checklists and standards as the database project itself
Change deployment scripts should also be provided by the development team and checked into a secure location (i.e. source control), so that all upgrades use a standard, reviewed copy of the script
Always understand the basic nature of the technologies used for development of a solution. These basic behavioural aspects have a significant impact on the overall behavior and quality of the solution

[The sample scripts and SSIS package referred to in the article are available as attachments]

About the Author

Nakul Vachhrajani is a SQL Server MCTS, TOGAF certified Technical Architect with Capgemini (formerly, IGATE) having a total IT experience of more than 11 years.

Nakul is an active blogger (http://nakulvachhrajani.com), and has been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide.

In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students.

A list of all my publications is available on my LinkedIn Profile

LinkedIn: https://in.linkedin.com/pub/nakul-vachhrajani/33/306/368
Twitter: @sqltwins

Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

SSIS: Case sensitivity may expose issues with change deployment