Parameterize Schema Name in SSDT Database Project .dacpac file by using DacFx Public Model and System.Reflection

• Download source - 35.7 KB

Introduction

Database project for Visual Studio (also known as SQL Server Data Tools, SSDT) is an extremely useful tool for database development. It allows code validation, syntax and referential integrity check during project build, database versioning, and many more.

But, of course, it has its limits. For one, there is very limited use of variables in object names. If you use project references, you can use variables in server name and database name in 4-part object names:

CREATE VIEW Sales.[FactEvent] AS
SELECT *
FROM
[$(SourceServer)].[$(SalesDatabase)].[dbo].[FactEvent]

But you cannot use variables in schema name, or table name, or view name, etc. There always will be an error 71502 "unresolved reference to object".

Fortunately, there is a way to overcome this limit. In the following article, I will explain how to edit a database package (.dacpac file), created by SSDT, how to rename a schema inside it, and deploy updated package to SQL Server.

We will use DacFx Public Model to parse database package and review database model inside it as an object hierarchy; and we will use Reflection to navigate and modify this hierarchy.

All code is written in C#.

Background: DacFx Public Model

DacFx public model is an API for Data-tier Application Framework. Data-tier Application Framework is just another name for object model used inside SSDT. In this model, each table, view, stored procedure, SELECT statement, etc. is represented as an object inside a complex hierarchy. For example, simple SELECT statement "select * from OldSchema.t1;" in DacFx public model looks like this, as viewed by Visual Studio debugger:

Each SQL statement would generate a different object hierarchy. We need a generic way to navigate through it recursively, find objects which have a specific schema name, and update it. If there are several such objects in the hierarchy (such as JOIN or UNION of several tables), we need to find all of them. Objects we are looking for might be located inside one another: view created from table, stored procedure which executes another stored procedure, etc. We need a reliable way to resolve all such cases. To achieve this, we will use System.Reflection.

Background: System.Reflection

Reflection is an API which allows the program to manipulate its own metadata. For example, Reflection allows us to analyze a class instance, get list of all its public properties and methods, change value of a property with specific name, invoke a method, etc.

private void DoSomething(dynamic obj)
{
   foreach (var property in obj.GetType().
      GetProperties(BindingFlags.Public | BindingFlags.Instance))
   {
      // Recursively analyse object model to find specific objects
   }
}

Using Reflection to Navigate DacFx Model

We will use the following algorithm to navigate DacFx model:

1. If current object has public properties which are DacFx objects, recursively run this algorithm for each of them
2. If current object is a collection, run this algorithm for each item in the collection
3. If current object has a schema, and its name is equal to oldSchema, update it to newSchema
4. If current object is a hardcoded text (such as "IF OBJECT_ID (N'OldSchema.ufn_SalesByStore', N'IF') IS NOT NULL"):
   • Split the text to words separated by dots
   • For each word, if it is equal to oldSchema, update it to newSchema

var propertyValue = index == -1 ? 
property.GetValue(obj) : property.GetValue(obj, new object[] { index });
//If we have a collection, then iterate through its elements.
bool isCollection = false;
if (property.PropertyType.GetInterface("System.Collections.IEnumerable") != null)
   isCollection = true;
string currentPropertyName = property.Name;
string currentPropertyType = property.PropertyType.Name;
string currentPropertyTypeNamespace = property.PropertyType.Namespace;
if (currentPropertyTypeNamespace == propertyTypeNamespace || 
	currentPropertyType == "String" || isCollection)
{
   if (isCollection && !(propertyValue is string) && propertyValue != null)
      foreach (var item in propertyValue)
         Set(item, oldValue, newValue, propertyTypeNamespace,
               propertyHierarchy + "." + currentPropertyName);
   //If field name and type matches, then set.
   else
      if (currentPropertyName == "Value" && 
      currentPropertyType == "String" && propertyValue == oldValue
            && (propertyHierarchy.EndsWith("MultiPartIdentifier.Item")
            || propertyHierarchy.EndsWith("SchemaIdentifier")
            || propertyHierarchy.EndsWith("CreateSchemaStatement.Name")))
         property.SetValue(obj, newValue);
      else
         if (currentPropertyName == "Value" && currentPropertyType == "String"
               && propertyHierarchy.EndsWith("Predicate.Expression.Parameters"))
            ProcessStringLiteral(propertyValue, property, obj, oldValue, newValue);
         else
            if (currentPropertyTypeNamespace == propertyTypeNamespace)
               Set(propertyValue, oldValue, newValue, propertyTypeNamespace, 
               propertyHierarchy + "." + currentPropertyName);
}

Why Visual Studio Deployment Contributor Won’t Do the Trick

My initial idea was to create Visual Studio deployment contributor – custom plugin for VS, which supplements deployment process. When SSDT generates deployment script, deployment contributor can modify the script before it is executed on server. Same approach (DacFx + Reflection) can be used to parse and modify the script, change schema name.

But there is a critical problem with this approach. Deployment script is based on comparison between source SSDT database model and target database model. This comparison happens before the deployment, so unchanged schema name is used for comparison. If target and source model already has equal definition of table OldSchema.t1, then this table will not be included into deployment script at all, so table NewSchema.t1 will not be created. On the other hand, if table OldSchema.t1 doesn’t exist in target, but table NewSchema.t1 does exist, then deployment attempt will fail with error “specified object already exists”.

So, we need to modify source model before the deployment and before model comparison. I tried to use Visual Studio build contributor for that. Similarly to deployment contributor, it supplements project build process. Unfortunately, build contributor has read-only access to SSDT database model. It cannot modify the model – only gather information about it, which could be used, for example, for custom logging.

So, the only way to modify the database model before the deployment is to edit database package file (.dacpac), and do it completely outside of Visual Studio deployment cycle. Modified package can then be deployed to SQL Server using SqlPackage.exe command line utility.

Parsing Database Package File

It is very easy to load model from .dacpac file:

TSqlModel modelFromDacpac = TSqlModel.LoadFromDacpac(fileName,
          new ModelLoadOptions(DacSchemaModelStorageType.Memory, loadAsScriptBackedModel: true));

It is important to specify option loadAsScriptBackedModel: true here. When this option is specified, each object in the model is loaded with auto-generated SQL script behind it. Method TsqlModel.DeleteObjects takes SQL script name as a parameter; so object that doesn’t have a script cannot be deleted from the model.

When the model is loaded, we can get a list of user defined objects in it. Before we can analyze each object as a class hierarchy, we need to convert it from TSqlObject to TSqlStatement. There is no native elegant way to do that, so we will have to use a trick: extract SQL script from TsqlObject, and build TsqlStatement on top of that script. To update the model, we will do reverse conversion.

It might be tempting to simply update SQL script on this step with functions like string.Replace(), without using DacFx class hierarchy at all. But if we do that, we might face lots of unexpected errors: some object names might include schema name as a substring; some object names might coincide with schema name, etc. If we use object model, we will be able to handle all such cases correctly.

List<TSqlObject> sourceModelObjects = new List<TSqlObject>();
foreach (TSqlObject tso in model.GetObjects(DacQueryScopes.UserDefined))
    sourceModelObjects.Add(tso);
for (int i = 0; i < sourceModelObjects.Count; i++)
{
    Console.WriteLine(DateTime.Now.ToString("T") + 
    " Processing object " + (i + 1).ToString() + " of " + 
       sourceModelObjects.Count.ToString() + ": " + 
       sourceModelObjects[i].ObjectType.Name + " " + sourceModelObjects[i].Name.ToString());
    SourceInformation sourceInfo = sourceModelObjects[i].GetSourceInformation();
    string oldObjectScript = string.Empty;
    sourceModelObjects[i].TryGetScript(out oldObjectScript);
    string newObjectScript = string.Empty;
    try
    {
        TSql120Parser parser = new TSql120Parser(false);
        IList<ParseError> errors;
        TSqlScript fragment = (TSqlScript)parser.Parse(new StringReader(oldObjectScript), out errors);
        TSqlStatement stmt = fragment.Batches[0].Statements[0];
        ReflectionUtils.SubstituteSchemaName(stmt, oldSchema,
                newSchema, "Microsoft.SqlServer.TransactSql.ScriptDom");
        Sql120ScriptGenerator sg = new Sql120ScriptGenerator();
        sg.GenerateScript(fragment, out newObjectScript);
    }
    catch (Exception ex) { }
    if (oldObjectScript != newObjectScript && newObjectScript != string.Empty)
    {
        model.DeleteObjects(sourceInfo.SourceName);
        model.AddObjects(newObjectScript);
    }
}

Saving updated model to the file is nearly as simple as loading it.

using (DacPackage dacPackage = DacPackage.Load(fileName,
                DacSchemaModelStorageType.Memory,
                FileAccess.ReadWrite))
   {
      DacPackageExtensions.UpdateModel(dacPackage, modelFromDacpac, null);
   }

Deploy Modified .dacpac File with SqlPackage.exe Utility

SqlPackage.exe command line utility is a part of SQL Server installation. It can be found at %Program Files (x86)\Microsoft SQL Server\120\DAC\bin\ (depending on version of SQL Server). In order to automate the deployment, I used PowerShell script to modify .dacpac and then deploy it:

$SqlPackageLocation = [string] ($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq "SqlPackageLocation" }).value
$DacpacFile = [string] ($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq " DacpacFile" }).value
$Profile = [string] ($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq "Profile" }).value
$SchemaSubstitute = [System.Convert]::ToBoolean
(($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq "SchemaSubstitute" }).value)
$SchemaSubstituteCmdLocation = [string] ($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq "SchemaSubstituteCmdLocation" }).value
$SchemaSubstituteParams = """$DacpacFile"""
foreach ($nodeXML1 in ($Settings.DeploymentSettings.Setting | ? 
{ $_.name -eq "SchemaSubstitutePairs" }).SchemaSubstitutePair)
{
    $SchemaSubstituteParams = $SchemaSubstituteParams + " " + 
    $nodeXML1.OldSchema + " " + $nodeXML1.NewSchema
}
if ($SchemaSubstitute) {
   write-host "Substituting schema with parameters " $SchemaSubstituteParams
   & $SchemaSubstituteCmdLocation $SchemaSubstituteParams
}
echo "Deploy"
& $SqlPackageLocation /Action:Publish /SourceFile:$DacpacFile /Profile:$Profile

Configuration file used by this script looks like this:

<DeploymentSettings>
  <Setting name="SqlPackageLocation" 
  value="C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe"/>
  <Setting name="DacpacFile" value="Sample.Database.dacpac"/>
  <Setting name="Profile" value="Sample.Database.publish.xml"/>
  <Setting name="SchemaSubstitute" value="True" />
  <Setting name="SchemaSubstituteCmdLocation" value="SchemaSubstituteCmd.exe" />
  <Setting name="SchemaSubstitutePairs">
         <SchemaSubstitutePair OldSchema="dbo" NewSchema="TestSchema" />
  </Setting>
</DeploymentSettings>

Conclusion

This application shows how to update model of a database package created by SSDT. Apart from parameterizing schema name, there are a lot of other potential use cases for this technology: change table name and columns, dynamically add new object such as index or default constraint, etc.

Credits

• Parameterize Schema Name within a SSDT Database Project for Multi-Tenant Solutions - this article and its source code was used as a starting point for this article
• This article is a nice tutorial for DacFx public model
• Reflection in C# Tutorial