/// <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);
}
