Import Table Script



Set a Database and one or more Table Names to import (correct location, correct schema)

published 04.19.14

last updated 04.19.14



  • Import
  • Script
  • Table

This script performs a fast Table import.  Set the Database Name and a single
or comma delimited list of Tables to import.  The script will generate the
BIML and create the file under the Tables directory of the specified Database.
It will also correct the Schema and add UsedByCycleTags.
20140418    david.darden    Initial Version 
// Set these Properties
string databaseName = "EDW_DM";
bool isDebug = true;

// Local variables
// We always want this UPPER because of case sensitivity when requesting it from NZ
// and our file naming convention.
tableNames = tableNames.ToUpper();
databaseName = databaseName.ToUpper();
var database = RootNode.Databases[databaseName];
var connection = database.Connection;
var model = ProjectManager.GetInstance().CurrentProjectViewModel;
var edw3RootDirectory = new DirectoryInfo(model.DirectoryPath).Parent.Parent.FullName; // We know it is 2 levels up based on our directory structure
string addUsedByCycleTagsPath = Path.Combine(edw3RootDirectory, "Framework", "BimlScripts", "Frame_E_Nodes_AddUsedByCycleTags.biml");
ImportResults results = null; 

// Get all the schemas for a connection
var schemas = connection.SchemaProvider.GetSchemaList(""); 

foreach(var tableName in tableNames.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
	string tableBiml = string.Empty;
	WriteLine("Looking for '{0}' in {1}", tableName, databaseName);

	// Loop through all the schemas and try and find a table
	foreach (var schema in schemas)
		results =  connection.ImportTableNodes(schema, tableName);
		if(results.TableNodes.Count() != 0)
			// If we found the table then save the biml and exit
			WriteLine("We found {0} in {1}", tableName, schema);
			tableBiml = "<Biml><Tables>\r\n" + results.TableNodes.GetBiml() + "\r\n</Tables></Biml>";	
				WriteLine("Nothing returned for {0}", schema);

	if (!String.IsNullOrEmpty(tableBiml))
		var databaseTablesDirectory = Path.Combine(Path.GetDirectoryName(database.BimlFile.FilePath), "Tables");
		var filename = tableName + ".biml";
		var filePath = Path.Combine(databaseTablesDirectory, filename);
		// Explicitly NOT deleting the file if it already exists; that might be a future improvement.  The Dev needs to handle that (in case they already added annotations, keys, comments, etc.)
		// This is present for developing/debugging
			WriteLine("The file already exists!  Delete it to continue (make sure you haven't modified it first!)");
			WriteLine("Writing the BIML to {0}", filePath);
			File.WriteAllText(filePath, tableBiml);

			WriteLine("Adding {0} to the project", tableName);
			System.Windows.Application.Current.Dispatcher.Invoke(new Action(() => { ProjectManager.GetInstance().Project.AddExistingPathToModel(filePath, Varigence.Utility.Files.ExplorerItemType.File); }));

			// The call above is async; wait just a second to let it complete and have the file added to the logical model
			// We've seen a few race conditions here, and it is possible Mist could crash or it won't find the object
			// if this is lower.

			var table = RootNode.Tables.FirstOrDefault(tbl => tbl.Name.Equals(tableName, StringComparison.InvariantCultureIgnoreCase));
			table.Schema = database.DefaultSchema;
		WriteLine("We did not find '{0}' in {1}.  Check your spelling and its existence.", tableName, databaseName);

WriteLine("Running 'Frame_E_Nodes_AddUsedByCycleTags' to add UsedByCycleTags");


<#@ template language="C#" hostspecific="True" #>
<#@ target type="Root" mergemode="RootMerge" #>
<#@ assembly name="C:\Program Files\Varigence\Mist\3.4\Mist.exe" #>
<#@ assembly name="C:\Program Files\Varigence\Mist\3.4\WpfControls.dll" #>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\PresentationFramework.dll" #>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Xaml.dll" #>
<#@ import namespace="Varigence.Mist.Managers" #>
<#@ import namespace="Varigence.Utility.Files" #>
<#@ import namespace="Varigence.Languages.Biml.Connection" #>
<#@ import namespace="Varigence.Hadron.Extensions" #>
<#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Threading" #>
<#@ annotation annotationtype="Description" tag="Summary" text="Set a Database and one or more Table Names to import (correct location, correct schema)." #>


This script performs a fast Table import of one or more Table Names for one of your databases. Set the Database Name and a singleor comma delimited list of Tables to import. The script will generate the BIML and create the file under the Tables directory of the specified Database. It will also change the schema to default. We use this since we typically know exactly what database / table we want to import and the Import Table process can be a little slow and it is hard to find the table you want out of several hundred.

The "FrameENodes_AddUsedByCycleTags" is custom to us and may not be useful to others; I left it in the script to demonstrate going from the location of a Project to a relative path to run a script (particularly useful when you use development branches so you don't have absolute file paths). This script includes some logic you may not need (such as the schema handling pieces) since we use Netezza which has some particular requirements/allowances in this area.

Note that this script modifies the project, and performs some operations that are not exactly standard (or expected by Mist). You can create a race condition that will cause a crash (pretty rare in our experience). So there's that. We found the speed of the operation made up for the occasional issue.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.


There are no comments yet.