/// <summary> /// Pre an dpost sql statements may contain placeholder for variables. /// Those placeholder will be replaced with variable values. /// </summary> /// <param name="templateStatement">sql statement</param> /// <returns>sql statement without placeholder</returns> public string GetExecuteStatementFromTemplate(string templateStatement) { string result = templateStatement; string varName = ""; try { if (result != "") { while (result.Contains("@(")) { IDTSVariables100 var = null; int start = result.IndexOf("@(", 0); int end = result.IndexOf(")", start); varName = result.Substring(start + 2, end - start - 2); _variableDispenser.LockOneForRead(varName, ref var); result = result.Replace("@(" + varName + ")", var[varName].Value.ToString()); var.Unlock(); } } } catch (Exception ex) { _events.Fire(IsagEvents.IsagEventType.ErrorVariableNotFound, "[{0}]: Variable not found: {1}", new string[] { "Pre-/PostSql", ex.Message }); } return(result); }
/// <summary> /// Return value of SSIS variable /// </summary> /// <param name="variableDispenser">SSIS variable dispenser</param> /// <param name="variableName">variable name</param> /// <returns>variable value</returns> public static string GetValueFromVariable(IDTSVariableDispenser100 variableDispenser, string variableName) { string result; IDTSVariables100 var = null; variableDispenser.LockOneForRead(variableName, ref var); result = var[variableName].Value.ToString(); var.Unlock(); return(result); }
public static object GetVariable(IDTSVariableDispenser100 vd, string varname, out DataType vartype) { object o = null; IDTSVariables100 vars = null; try { vd.LockOneForRead(varname, ref vars); o = vars[varname].Value; vartype = (DataType)vars[varname].DataType; return(o); } finally { if (vars != null) { vars.Unlock(); } } }
/// <summary> /// Replace placeholders in sql statments with variable values /// </summary> /// <param name="templateTableName">sql statement template</param> /// <returnsexecutable sql statementtatement</returns> public static string GetTableExpressionFromTemplate(string templateTableName, IDTSVariableDispenser100 variableDispenser, IDTSComponentMetaData100 componentMetaData) { if (templateTableName == "") { return(""); } string result = templateTableName; try { if (result != "") { while (result.Contains("@(")) { string varName = ""; IDTSVariables100 var = null; int start = result.IndexOf("@(", 0); int end = result.IndexOf(")", start); varName = result.Substring(start + 2, end - start - 2); variableDispenser.LockOneForRead(varName, ref var); result = result.Replace("@(" + varName + ")", var[varName].Value.ToString()); var.Unlock(); } } } catch (Exception ex) { Events.Fire(componentMetaData, Events.Type.Error, string.Format("[{0}]: Variable not found: {1}", "Pre-/PostSql", ex.Message)); } return(result); }