public static void ProcessTSqlObjectIntoDimensionScriptFiles(ParsedArgs parsedArgs, TSqlObject table, ModelRelationshipClass relationshipTypeColumns, ModelRelationshipClass relationshipTypeSchema) { string stagingSchemaName = GetSchemaName(table); string templateDimCoreName = GetObjectName(table).Replace("_dimSrc_stg", ""); List <String> listOfColumns = new List <String>(); foreach (var col in table.GetReferenced(relationshipTypeColumns, DacQueryScopes.UserDefined)) { String column = GetColumnName(col); listOfColumns.Add(column); } GenerateDimension(listOfColumns, templateDimCoreName, parsedArgs, stagingSchemaName); }
static void Main(string[] args) { ParsedArgs parsedArgs = new ParsedArgs(args); Microsoft.SqlServer.Dac.Model.TSqlModel sqlModel = new Microsoft.SqlServer.Dac.Model.TSqlModel(parsedArgs.DacPacFileName); //Where takes a predicate thing. No, I haven't figured out how to do that without lambda stuff yet. But this is tolerably readable for main control flow, I think. var tables = sqlModel.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList().Where(table => table.Name.ToString().EndsWith("_dimSrc_stg]")); var views = sqlModel.GetObjects(DacQueryScopes.Default, View.TypeClass).ToList().Where(view => view.Name.ToString().EndsWith("_dimSrc_stg]")); foreach (var table in tables) { ProcessTSqlObjectIntoDimensionScriptFiles(parsedArgs, table, Table.Columns, Table.Schema); } foreach (var view in views) { ProcessTSqlObjectIntoDimensionScriptFiles(parsedArgs, view, View.Columns, View.Schema); } Console.WriteLine("Press any key to close!"); Console.ReadLine(); }
public static void GenerateDimension(List <string> listOfColumns, String templateDimCoreName, ParsedArgs parsedArgs, String StagingSchema) { List <String> listOfNks = listOfColumns.Where(mystring => mystring.StartsWith("NK_")).ToList <String>(); LineProcessorConfig lineProcessorConfigNK = new LineProcessorConfig("NaturalKey_ReplacementPoint", listOfNks); List <String> listOfDims = listOfColumns.Where(mystring => !mystring.StartsWith("Ctl_")).Where(mystring => !mystring.StartsWith("NK_")).ToList <String>(); LineProcessorConfig lineProcessorConfigDim = new LineProcessorConfig("DimensionAttribute_ReplacementPoint", listOfDims); List <String> listOfCtl = listOfColumns.Where(mystring => mystring.StartsWith("Ctl_")).ToList <String>(); //We should error out if table/view exists and listOfCtl <> List<String>{"Ctl_EffectiveDate"} //Something like: //method list all files in template directory to output directory, get string renaming them to swap out the templateDimCoreName in the file name. //StreamReader the source file. Foreach line, create line processor for nk, get back line. Create line processor for dim (with that returned line), get back line. //StreamWriter out the line to a file with the new name in output directory. // List <String> templateFiles = new List <string> { "Stored Procedures\\templateDimCoreName_dimSetup_Idempotent_MissingMember_usp.sql" , "Stored Procedures\\templateDimCoreName_dimUpsert_Step1_Update_usp.sql" , "Stored Procedures\\templateDimCoreName_dimUpsert_Step2_Insert_usp.sql" , "Stored Procedures\\templateDimCoreName_orchestration_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_orchestration_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_setup_clearTables_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_Step1_copycurrent_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_Step2_prep_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_Step3_prep_updateSCDStatus_usp.sql" , "Stored Procedures\\templateDimCoreName_predim_Step4_prep_DeleteIgnorable.sql" , "Stored Procedures\\templateDimCoreName_predim_Step5_Check_OneRowPerKey_usp.sql" //,"Tables\\templateDimCoreName_dimSrc_stg.sql" , "Tables\\templateDimCoreName_predim_copycurrent.sql" , "Tables\\templateDimCoreName_predim_prep.sql" , "Views\\templateDimCoreName_dimUpsert_GetMaxSK_vw.sql" , "Views\\templateDimCoreName_dimUpsert_Step2_Insert_SelectClause_vw.sql" , "Views\\templateDimCoreName_FindUpdates_vw.sql" , "Views\\templateDimCoreName_predim_Step2_prep_columnTransformations_vw.sql" }; foreach (String file in templateFiles) { TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6TemplateDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, file); } string dimFile = "Tables\\templateDimCoreName_Dim.sql"; TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6DimensionDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, dimFile); string dimSKLookupFile = "Functions\\templateDimCoreName_DimSKLookup_usvf.sql"; TransformFileInFlight(templateDimCoreName, parsedArgs.SCDType6DimensionDirectory, parsedArgs.OutputDirectory, parsedArgs.DimensionSchema, parsedArgs.templateSchema, StagingSchema, lineProcessorConfigNK, lineProcessorConfigDim, dimSKLookupFile); Console.WriteLine(templateDimCoreName); Console.WriteLine(String.Join("\r\n", lineProcessorConfigDim.perLineSubstitutions.ToArray())); Console.WriteLine(String.Join("\r\n", lineProcessorConfigNK.perLineSubstitutions.ToArray())); }