<# /*============================================================================= Description: 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. =============================================================================== History: 20140418 david.darden Initial Version =============================================================================*/ #> <# WriteLine("<!--"); // Set these Properties string databaseName = "EDW_DM"; string tableNames = "TMP_DNR_PROSPECT_CUSTOMER_LAST_GENRE_DOWNLOAD,DNR_PROSPECT_CUSTOMER_BCK_20130830"; 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>"; break; } else { if(isDebug) { 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 if(File.Exists(filePath)) { //File.Delete(filePath); WriteLine("The file already exists! Delete it to continue (make sure you haven't modified it first!)"); } else { 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. Thread.Sleep(1000); var table = RootNode.Tables.FirstOrDefault(tbl => tbl.Name.Equals(tableName, StringComparison.InvariantCultureIgnoreCase)); table.Schema = database.DefaultSchema; } } else { 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"); this.CallBimlScript(addUsedByCycleTagsPath); WriteLine("-->"); #> <#@ 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)." #> <Biml></Biml>
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.
Comments
There are no comments yet.