private static void _processReadMetadata(AstReadPersistentVariableNode astNode) { var workflowFragment = new List <AstTaskNode>(); var parentContainer = (AstContainerTaskNode)astNode.ParentItem; var execSQL = new AstExecuteSqlTaskNode(parentContainer) { Name = astNode.Name, Connection = astNode.Connection, ResultSet = ExecuteSqlResultSet.None }; execSQL.Query = new AstExecuteSqlQueryNode(execSQL) { Body = String.Format(CultureInfo.InvariantCulture, "\"EXECUTE [usp_VulcanLog_ReadMetadata] ? OUTPUT, '{0}'\"", astNode.PersistentVariable.ScopedName), QueryType = QueryType.Expression }; execSQL.Query.Parameters.Add( new AstExecuteSqlParameterMappingTypeNode(execSQL) { Name = "0", Variable = astNode.TargetVariable, Direction = Direction.Output, Length = 9999 }); workflowFragment.Add(execSQL); parentContainer.Tasks.Replace(astNode, workflowFragment); }
private void LoadParameterMappings(AstExecuteSqlTaskNode astNode) { foreach (AstExecuteSqlParameterMappingTypeNode paramNode in astNode.Query.Parameters) { var paramType = PlatformEmitters.OleDBTypeTranslator.Translate(paramNode.Variable.TypeCode); DTSTasks.ExecuteSQLTask.ParameterDirections direction; switch (paramNode.Direction) { case Direction.Input: direction = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input; break; case Direction.Output: direction = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output; break; case Direction.ReturnValue: direction = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.ReturnValue; break; default: throw new NotImplementedException(String.Format(CultureInfo.InvariantCulture, "ExecuteSQL Parameters: Direction {0} not supported", paramNode.Direction)); } _parameters.Add(paramNode.Name, new ExecuteSqlParameter(paramNode.Name, paramNode.Variable.Name, direction, paramType, paramNode.Length)); } }
private void LoadResultMappings(AstExecuteSqlTaskNode astNode) { foreach (AstExecuteSqlParameterMappingTypeNode resultNode in astNode.Results) { _executeSQLResultList.Add(resultNode.Name, new ExecuteSqlResult(resultNode.Name, resultNode.Variable.Name)); } }
// TODO: We've made the call to just use the staging node as the lowered container rather than creating a new one and copying everything over public static void ProcessContainers(SymbolTable symbolTable) { var snapshotSymbolTable = new List <IReferenceableItem>(symbolTable); foreach (var astNamedNode in snapshotSymbolTable) { var stagingNode = astNamedNode as AstStagingContainerTaskNode; if (stagingNode != null && astNamedNode.FirstThisOrParent <ITemplate>() == null) { var stagingCreateContainer = new AstContainerTaskNode(stagingNode) { Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStaging, stagingNode.Name), Log = false, }; var stagingDropContainer = new AstContainerTaskNode(stagingNode) { Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStaging, stagingNode.Name), Log = false, }; stagingNode.Tasks.Insert(0, stagingCreateContainer); stagingNode.Tasks.Add(stagingDropContainer); foreach (var baseTable in stagingNode.Tables) { var table = baseTable as AstTableNode; if (table != null) { TableLowerer.LowerTable( stagingCreateContainer, table, String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStagingTable, table.Name), stagingNode.ExecuteDuringDesignTime); var dropStagingTemplate = new TemplatePlatformEmitter("DropStagingTable", table.SchemaQualifiedName); var dropTableExecuteSqlTask = new AstExecuteSqlTaskNode(stagingNode) { Name = StringManipulation.NameCleanerAndUniqifier(String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStagingTable, table.Name)), Connection = table.Connection, ExecuteDuringDesignTime = stagingNode.ExecuteDuringDesignTime, }; dropTableExecuteSqlTask.Query = new AstExecuteSqlQueryNode(dropTableExecuteSqlTask) { QueryType = QueryType.Standard, Body = dropStagingTemplate.Emit() }; stagingDropContainer.Tasks.Add(dropTableExecuteSqlTask); } else { throw new System.NotSupportedException("AstLowering - StagingContainer - a Table Template node was found when lowering staging containers and I don't know what to do with it."); } } } } }
public static void ProcessPrincipals(AstRootNode astRootNode) { if (astRootNode.Principals.Count > 0) { var package = new AstPackageNode(astRootNode) { Name = "PrincipalsInitializer", Emit = true, PackageType = "Principal" }; astRootNode.Packages.Add(package); foreach (AstPrincipalNode principal in astRootNode.Principals) { TemplatePlatformEmitter principalTemplate; switch (principal.PrincipalType) { case PrincipalType.ApplicationRole: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalApplicationRole", principal.Name); break; case PrincipalType.DBRole: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalDatabaseRole", principal.Name); break; case PrincipalType.SqlUser: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalSqlUser", principal.Name); break; case PrincipalType.WindowsGroup: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalWindowsUser", principal.Name); break; case PrincipalType.WindowsUser: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalWindowsUser", principal.Name); break; default: MessageEngine.Trace(principal, Severity.Error, "V0139", "Unknown Principal Type {0} in principal {1}", principal.PrincipalType.ToString(), principal.Name); return; } var executeSqlTask = new AstExecuteSqlTaskNode(package) { Name = Utility.NameCleanerAndUniqifier(String.Format(CultureInfo.InvariantCulture, "PrincipalConfig_{0}", principal.Name)), Connection = principal.Connection }; executeSqlTask.Query = new AstExecuteSqlQueryNode(executeSqlTask) { Body = principalTemplate.Emit(), QueryType = QueryType.Standard }; package.Tasks.Add(executeSqlTask); } } }
public static void ProcessSchema(AstRootNode astRootNode, AstSchemaNode schemaNode) { const string PackageTypeName = "Schema"; var packageNode = new AstPackageNode(schemaNode.ParentItem); packageNode.Name = schemaNode.Name; packageNode.PackageType = PackageTypeName; var executeSqlNode = new AstExecuteSqlTaskNode(packageNode) { Name = schemaNode.Name, Connection = schemaNode.Connection, ResultSet = ExecuteSqlResultSet.None }; executeSqlNode.Query = new AstExecuteSqlQueryNode(executeSqlNode) { QueryType = QueryType.Standard, Body = new TemplatePlatformEmitter("CreateSchema", schemaNode.Name).Emit() }; packageNode.Tasks.Add(executeSqlNode); bool hasPermissions = false; var permissionBuilder = new StringBuilder(); foreach (var permission in schemaNode.Permissions) { hasPermissions = true; permissionBuilder.AppendLine(PermissionsLowerer.ProcessPermission(schemaNode, permission)); } if (hasPermissions) { var permissionsExecuteSqlTask = new AstExecuteSqlTaskNode(packageNode) { Name = "__SetPermissions", Connection = schemaNode.Connection, }; permissionsExecuteSqlTask.Query = new AstExecuteSqlQueryNode(permissionsExecuteSqlTask) { Body = permissionBuilder.ToString(), QueryType = QueryType.Standard }; packageNode.Tasks.Add(permissionsExecuteSqlTask); } if (schemaNode.CustomExtensions != null) { packageNode.Tasks.Add(schemaNode.CustomExtensions); } astRootNode.Packages.Add(packageNode); }
public SqlTask(AstExecuteSqlTaskNode astNode) : base(astNode) { ExecuteDuringDesignTime = astNode.ExecuteDuringDesignTime; _body = astNode.Query.Body; _connection = new OleDBConnection(astNode.Connection); StatementType = astNode.Query.QueryType == QueryType.Expression ? SqlTaskStatementType.Expression : SqlTaskStatementType.File; ResultSetType = (SqlTaskResultSetType)Enum.Parse(typeof(SqlTaskResultSetType), astNode.ResultSet.ToString(), true); LoadResultMappings(astNode); LoadParameterMappings(astNode); }
private static void _packageBuildOnPreExecuteEvent(AstPackageBaseNode package) { var packageEvent = ContainerFindEvent(package.Events, EventType.OnPreExecute); if (packageEvent == null) { packageEvent = new AstTaskEventHandlerNode(package) { EventType = EventType.OnPreExecute }; package.Events.Add(packageEvent); } var preExecContainer = new AstContainerTaskNode(packageEvent) { Name = "OnPreExec", ConstraintMode = ContainerConstraintMode.Linear }; packageEvent.Tasks.Insert(0, preExecContainer); var constrainedCont = new AstContainerTaskNode(packageEvent) { Name = "ConstrainedContainer", ConstraintMode = ContainerConstraintMode.Parallel }; preExecContainer.Tasks.Add(constrainedCont); var executeSql = new AstExecuteSqlTaskNode(preExecContainer) { Name = "Exec usp_PackageStart", ExecuteDuringDesignTime = false, Connection = package.LogConnection }; executeSql.PrecedenceConstraints = new AstTaskflowPrecedenceConstraintsNode(executeSql); executeSql.PrecedenceConstraints.Inputs.Add( new AstTaskflowInputPathNode(executeSql.PrecedenceConstraints) { Expression = "@[System::PackageID] == @[System::SourceID]", EvaluationOperation = TaskEvaluationOperationType.Expression, OutputPath = constrainedCont.OutputPath }); executeSql.Query = new AstExecuteSqlQueryNode(executeSql) { Body = "\"EXEC usp_PackageStart \" + (@[System::PackageName] == \"\" ? \"NULL\" : \"'\"+@[System::PackageName]+\"'\") +\",\"+(@[System::PackageID] == \"\" ? \"NULL\" : \"'\"+@[System::PackageID]+\"'\") +\",\"+(@[User::_parentPackageGuid] == \"\" ? \"NULL\" : \"'\"+@[User::_parentPackageGuid]+\"'\") +\",\"+(@[System::SourceName] == \"\" ? \"NULL\" : \"'\"+@[System::SourceName]+\"'\") +\",\"+(@[System::SourceID] == \"\" ? \"NULL\" : \"'\"+@[System::SourceID]+\"'\") +\",\"+(@[System::SourceParentGUID] == \"\" ? \"NULL\" : \"'\"+@[System::SourceParentGUID]+\"'\") +\",\"+ (@[System::MachineName] == \"\" ? \"NULL\" : \"'\"+@[System::MachineName]+\"'\") +\",\"+(@[System::UserName] == \"\" ? \"NULL\" : \"'\"+@[System::UserName]+\"'\") +\",\"+(@[User::_patchedExecutionGuid] == \"\" ? \"NULL\" : \"'\"+@[User::_patchedExecutionGuid])+\"'\"", QueryType = QueryType.Expression }; preExecContainer.Tasks.Add(executeSql); }
public static AstExecuteSqlTaskNode CreateInsertExecuteSql(AstTableStaticSourceNode staticSource, IFrameworkItem insertParent, AstTableNode insertTargetTable) { var executeSql = new AstExecuteSqlTaskNode(insertParent) { Connection = insertTargetTable.Connection, ExecuteDuringDesignTime = false, Name = Utility.NameCleanerAndUniqifier(insertTargetTable + "_Insert"), }; executeSql.Query = new AstExecuteSqlQueryNode(executeSql) { Body = GetInsertStatements(insertTargetTable, staticSource), QueryType = QueryType.Standard }; return(executeSql); }
private static void _processWriteMetadata(AstWritePersistentVariableNode astNode) { var workflowFragment = new List <AstTaskNode>(); var parentContainer = (AstContainerTaskNode)astNode.ParentItem; var writePresistentVariableID = new AstVariableNode(parentContainer) { Name = _generateScopedVariableName(astNode.PersistentVariable), TypeCode = TypeCode.String, Value = String.Empty }; parentContainer.Variables.Add(writePresistentVariableID); var execSqlwritePersistentVariable = new AstExecuteSqlTaskNode(parentContainer) { Name = astNode.Name, Connection = astNode.Connection, ResultSet = ExecuteSqlResultSet.None }; execSqlwritePersistentVariable.Query = new AstExecuteSqlQueryNode(execSqlwritePersistentVariable) { Body = String.Format( CultureInfo.InvariantCulture, "\"EXECUTE [usp_VulcanLog_WriteMetadata] ? OUTPUT, '{0}', '\"+ (DT_WSTR, 2000)@[User::{1}] + \"', {2}\"", astNode.PersistentVariable.ScopedName, astNode.SourceVariable.Name, Convert.ToInt32(astNode.Commit)), QueryType = QueryType.Expression }; execSqlwritePersistentVariable.Query.Parameters.Add( new AstExecuteSqlParameterMappingTypeNode(execSqlwritePersistentVariable) { Name = "0", Variable = writePresistentVariableID, Direction = Direction.Output, Length = 255 }); workflowFragment.Add(execSqlwritePersistentVariable); parentContainer.Tasks.Replace(astNode, workflowFragment); }
// TODO: Is this the right approach for events and precedence constraints? Should we have a utility method to handle them? // TODO: It would be good to find an approach to unify permissions and move some of this under the securables lowerer. public static void ProcessStoredProcedure(AstStoredProcNode storedProcNode) { var executeSqlNode = new AstExecuteSqlTaskNode(storedProcNode.ParentItem) { Name = storedProcNode.Name, ExecuteDuringDesignTime = storedProcNode.ExecuteDuringDesignTime, Connection = storedProcNode.Connection, ResultSet = ExecuteSqlResultSet.None }; executeSqlNode.Query = new AstExecuteSqlQueryNode(executeSqlNode) { QueryType = QueryType.Standard }; var queryBuilder = new StringBuilder(new StoredProcTSqlEmitter(storedProcNode).Emit()); foreach (var permission in storedProcNode.Permissions) { var template = new TemplatePlatformEmitter("CreateStoredProcedurePermission", permission.Action.ToString(), permission.Target.ToString(), storedProcNode.Name, permission.Principal.Name); queryBuilder.AppendLine(template.Emit()); } executeSqlNode.Query.Body = queryBuilder.ToString(); executeSqlNode.PrecedenceConstraints = storedProcNode.PrecedenceConstraints; if (executeSqlNode.PrecedenceConstraints != null) { executeSqlNode.PrecedenceConstraints.ParentItem = executeSqlNode; } foreach (var eventHandler in storedProcNode.Events) { executeSqlNode.Events.Add(eventHandler); eventHandler.ParentItem = executeSqlNode; } var parentContainer = storedProcNode.ParentItem as AstContainerTaskNode; if (parentContainer != null) { parentContainer.Tasks.Replace(storedProcNode, executeSqlNode); } }
private static void _processCommitMetadata(AstCommitPersistentVariableNode astNode) { var workflowFragment = new List <AstTaskNode>(); var parentContainer = (AstContainerTaskNode)astNode.ParentItem; var scopedVariableName = _generateScopedVariableName(astNode.PersistentVariable); bool insideScope = false; // TODO: bugbug - replace with the proper symbol table to search child scopes. This is only a cursory check. foreach (AstVariableNode v in parentContainer.Variables) { if (v.Name.Equals(scopedVariableName)) { insideScope = true; break; } } if (!insideScope) { MessageEngine.Trace( Severity.Warning, "LOG:MC01 Metadata Commit for {0} in task {1} may fail. Metadata should be committed in the same container scope as the metadata write.", astNode.PersistentVariable, astNode.Name); } var execSQL = new AstExecuteSqlTaskNode(parentContainer) { Name = astNode.Name, Connection = astNode.Connection, ResultSet = ExecuteSqlResultSet.None }; execSQL.Query = new AstExecuteSqlQueryNode(execSQL) { Body = String.Format(CultureInfo.InvariantCulture, "\"EXECUTE [usp_VulcanLog_CommitMetadata] '\"+ (DT_WSTR, 255)@[User::{0}] + \"'\"", scopedVariableName), QueryType = QueryType.Expression }; workflowFragment.Add(execSQL); parentContainer.Tasks.Replace(astNode, workflowFragment); }
// TODO: Is this the right approach for events and precedence constraints? Should we have a utility method to handle them? public static void ProcessMerge(AstMergeTaskNode mergeNode) { var executeSqlNode = new AstExecuteSqlTaskNode(mergeNode.ParentItem) { Name = mergeNode.Name, ExecuteDuringDesignTime = false, Connection = ((AstTableNode)mergeNode.TargetConstraint.ParentItem).Connection, ResultSet = ExecuteSqlResultSet.None, DelayValidation = mergeNode.DelayValidation, IsolationLevel = mergeNode.IsolationLevel }; executeSqlNode.Query = new AstExecuteSqlQueryNode(executeSqlNode) { QueryType = QueryType.Standard, Body = new MergeTSqlEmitter(mergeNode).Emit() }; executeSqlNode.PrecedenceConstraints = mergeNode.PrecedenceConstraints; if (executeSqlNode.PrecedenceConstraints != null) { executeSqlNode.PrecedenceConstraints.ParentItem = executeSqlNode; } foreach (var eventHandler in mergeNode.Events) { executeSqlNode.Events.Add(eventHandler); eventHandler.ParentItem = executeSqlNode; } var parentContainer = mergeNode.ParentItem as AstContainerTaskNode; if (parentContainer != null) { parentContainer.Tasks.Replace(mergeNode, executeSqlNode); } }
internal static void LowerTable(AstContainerTaskNode containerNode, AstTableNode tableNode, string executeSqlTaskName, bool executeDuringDesignTime) { var tableEmitter = new TableTSqlEmitter(tableNode.SchemaQualifiedName, tableNode.CompressionType.ToString().ToUpper(CultureInfo.InvariantCulture)); foreach (AstTableColumnBaseNode columnBase in tableNode.Columns) { ProcessAstTableColumnBaseNode(tableNode, tableEmitter.ColumnsEmitter, columnBase); var tableReference = columnBase as AstTableColumnTableReferenceNode; var dimReference = columnBase as AstTableColumnDimensionReferenceNode; if (tableReference != null && tableReference.EnforceForeignKeyConstraint) { tableEmitter.ConstraintsEmitter.AppendForeignKeyConstraintFromReference(tableNode, tableReference.ForeignKeyNameOverride, tableReference.Name, tableReference.Table); } if (dimReference != null && dimReference.EnforceForeignKeyConstraint) { tableEmitter.ConstraintsEmitter.AppendForeignKeyConstraintFromReference(tableNode, dimReference.ForeignKeyNameOverride, dimReference.Name, dimReference.Dimension); } } foreach (AstTableKeyBaseNode keyBase in tableNode.Keys) { tableEmitter.ConstraintsEmitter.AppendConstraint(keyBase); } foreach (AstTableIndexNode index in tableNode.Indexes) { tableEmitter.ConstraintsEmitter.AppendIndex(tableNode.SchemaQualifiedName, index); } // TODO: Fix this null parent node var createTableExecuteSqlTaskNode = new AstExecuteSqlTaskNode(containerNode) { Name = StringManipulation.NameCleanerAndUniqifier(executeSqlTaskName), ResultSet = ExecuteSqlResultSet.None, Connection = tableNode.Connection, ExecuteDuringDesignTime = executeDuringDesignTime }; createTableExecuteSqlTaskNode.Query = new AstExecuteSqlQueryNode(createTableExecuteSqlTaskNode) { QueryType = QueryType.Standard, Body = tableEmitter.Emit() }; containerNode.Tasks.Add(createTableExecuteSqlTaskNode); bool hasPermissions = false; var permissionBuilder = new StringBuilder(); foreach (var permission in tableNode.Permissions) { hasPermissions = true; permissionBuilder.AppendLine(PermissionsLowerer.ProcessPermission(tableNode, permission)); } foreach (var column in tableNode.Columns) { foreach (var permission in column.Permissions) { hasPermissions = true; permissionBuilder.AppendLine(PermissionsLowerer.ProcessPermission(column, permission)); } } if (hasPermissions) { var permissionsExecuteSqlTask = new AstExecuteSqlTaskNode(containerNode) { Name = "__SetPermissions", Connection = tableNode.Connection, }; permissionsExecuteSqlTask.Query = new AstExecuteSqlQueryNode(permissionsExecuteSqlTask) { Body = permissionBuilder.ToString(), QueryType = QueryType.Standard }; containerNode.Tasks.Add(permissionsExecuteSqlTask); } if (tableNode.CustomExtensions != null) { containerNode.Tasks.Add(tableNode.CustomExtensions); } foreach (var source in tableNode.Sources) { var staticSource = source as AstTableStaticSourceNode; if (staticSource != null && staticSource.Rows.Count > 0) { if (staticSource.EmitMergePackage) { // TODO: This is nasty - we need a way to reference packages and emit paths at lowering time var executeMergePackage = new AstExecutePackageTaskNode(containerNode); executeMergePackage.Name = "__ExecuteMergePackage"; executeMergePackage.Package = staticSource.LoweredPackage; containerNode.Tasks.Add(executeMergePackage); } else { containerNode.Tasks.Add(StaticSourcesLowerer.CreateInsertExecuteSql(staticSource, containerNode, tableNode)); } } } }
private static void _packageBuildOnErrorEvent(AstPackageBaseNode package) { var packageEvent = ContainerFindEvent(package.Events, EventType.OnError); if (packageEvent == null) { packageEvent = new AstTaskEventHandlerNode(package) { EventType = EventType.OnError }; package.Events.Add(packageEvent); } var executeSql = new AstExecuteSqlTaskNode(packageEvent) { Name = "Exec usp_PackageError", ExecuteDuringDesignTime = false, Connection = package.LogConnection }; executeSql.Query = new AstExecuteSqlQueryNode(executeSql) { Body = "\"EXECUTE [usp_PackageError] ?,?,?,?,?,?,?,?,?,? \"", QueryType = QueryType.Expression }; executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "0", Direction = Direction.Input, Variable = packageName, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "1", Direction = Direction.Input, Variable = packageGuid, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "2", Direction = Direction.Input, Variable = parentPackageGuid, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "3", Direction = Direction.Input, Variable = source, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "4", Direction = Direction.Input, Variable = sourceID, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "5", Direction = Direction.Input, Variable = parentSourceID, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "6", Direction = Direction.Input, Variable = machineName, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "7", Direction = Direction.Input, Variable = userName, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "8", Direction = Direction.Input, Variable = patchedExecutionGuid, Length = 255 }); executeSql.Query.Parameters.Add(new AstExecuteSqlParameterMappingTypeNode(executeSql.Query) { Name = "9", Direction = Direction.Input, Variable = errorDescription, Length = -1 }); packageEvent.Tasks.Insert(0, executeSql); }