/// <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 DataTable GetDataTableWithInputVar(String filePathVar, String sheetName)
{
IDTSVariables100 vars = null;
VariableDispenser.LockForRead(filePathVar);
VariableDispenser.GetVariables(out vars);
string filePath = (String)vars[filePathVar].Value;
vars.Unlock();
DataTable dt = ExcelService.ReadExcel(filePath, sheetName, 1);
return(dt);
}
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);
}
// The main data flow function
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
// Get teh model
JSONDataModel model = getModel();
// Initialize Couchbase Client
CouchbaseClientConfiguration config = new CouchbaseClientConfiguration();
config.Urls.Add(new Uri(ComponentMetaData.CustomPropertyCollection["url"].Value.ToString().TrimEnd('/') + "/pools/"));
config.Bucket = ComponentMetaData.CustomPropertyCollection["bucket"].Value.ToString();
config.BucketPassword = ComponentMetaData.CustomPropertyCollection["password"].Value.ToString();
CouchbaseClient client = new CouchbaseClient(config);
// Extract the parameters
string designDoc = ComponentMetaData.CustomPropertyCollection["designDoc"].Value.ToString();
string viewName = ComponentMetaData.CustomPropertyCollection["view"].Value.ToString();
bool forceReindex = (bool)ComponentMetaData.CustomPropertyCollection["forceReindex"].Value;
bool descending = (bool)ComponentMetaData.CustomPropertyCollection["descending"].Value;
// Define the view to be executed
IView <IViewRow> view = ((IView <IViewRow>)client.GetView(designDoc, viewName))
.Stale(forceReindex ? StaleMode.False : StaleMode.AllowStale)
.Descending(descending);
// Extract the variables from the package
IDTSVariables100 variables = null;
// StartKey can be set from another task prior of running this task
string startKey = ComponentMetaData.CustomPropertyCollection["startKey"].Value;
if (startKey != null && startKey.StartsWith("@"))
{
VariableDispenser.LockOneForRead(startKey.Substring(1), ref variables);
startKey = variables[0].Value.ToString();
variables.Unlock();
ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable StartKey. Using " + startKey + " as value.", DateTime.Now, DateTime.Now, 0, null);
}
// EndKey can be set from another task prior of running this task
string endKey = ComponentMetaData.CustomPropertyCollection["endKey"].Value;
if (endKey != null && endKey.StartsWith("@"))
{
VariableDispenser.LockOneForRead(endKey.Substring(1), ref variables);
endKey = variables[0].Value.ToString();
variables.Unlock();
ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable EndKey. Using " + endKey + " as value.", DateTime.Now, DateTime.Now, 0, null);
}
// Apply variables to the view if necessary
if (startKey != null && !startKey.Equals(""))
{
view = view.StartKey <string>(startKey);
}
if (endKey != null && !endKey.Equals(""))
{
view = view.EndKey <string>(endKey);
}
// Iterate over each document returned by the view
foreach (IViewRow row in view)
{
// Say that we have read it
ComponentMetaData.IncrementPipelinePerfCounter(101, 1);
// Write it out to the outputs
writeDocToBuffers(row.ItemId, row.GetItem().ToString(), model, outputIDs, buffers);
// Say that we wrote it
ComponentMetaData.IncrementPipelinePerfCounter(103, 1);
}
// Flush out all buffers and get outta here
foreach (PipelineBuffer buffer in buffers)
{
/// Notify the data flow task that no more rows are coming.
buffer.SetEndOfRowset();
}
}
public override void PreExecute()
{
bool debugging = false;
IDTSVariables100 vars = null;
try
{
VariableDispenser.LockOneForRead(JSON_SOURCE_DEBUG_VAR, ref vars);
object o = vars[JSON_SOURCE_DEBUG_VAR].Value;
if (o != null)
{
if ((bool)o)
{
debugging = true;
}
}
}
catch (Exception e) {
//Do nothing
bool fireAgain = false;
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "wk_debug variable cannot be found. I won't stop to let debug attachment.", null, 0, ref fireAgain);
}
finally
{
if (vars != null)
{
vars.Unlock();
}
}
if (debugging)
{
MessageBox.Show("Start Debugger");
}
TransformationModel m = GetModel();
_opt = new ParallelOptions();
_opt.MaxDegreeOfParallelism = 4;
bool cancel = false;
// Carico i dettagli dal model
try{
m = GetModel();
}catch (ModelNotFoundException ex) {
ComponentMetaData.FireError(RUNTIME_ERROR_MODEL_INVALID, ComponentMetaData.Name, "Invalid Metadata for this component.", null, 0, out cancel);
return;
}
// Salva il mapping in un array locale
_iomap = m.IoMap.ToArray <IOMapEntry>();
// Salva una copia locale del percorso cui attingere l'array
_pathToArray = m.JsonObjectRelativePath;
// Genera un dizionario ad accesso veloce per il nome della colonna per i dati json: mappo nome colonna - Indice della colonna nella riga.
// Questo dizionario è usato solo per il JSON, mentre per gli input standard non facciamo il lookup, ma usiamo l'indice del buffer.
_startOfJsonColIndex = ComponentMetaData.InputCollection[0].InputColumnCollection.Count;
_outColsMaps = new Dictionary <string, int>();
foreach (IOMapEntry e in _iomap)
{
bool found = false;
for (var i = 0; i < _iomap.Count(); i++)
{
var col = ComponentMetaData.OutputCollection[0].OutputColumnCollection[_startOfJsonColIndex + i];
if (col.Name == e.OutputColName)
{
found = true;
int colIndex = BufferManager.FindColumnByLineageID(ComponentMetaData.OutputCollection[0].Buffer, col.LineageID);
_outColsMaps.Add(e.OutputColName, colIndex);
break;
}
}
if (!found)
{
// Una colonna del model non ha trovato il corrispettivo nel componente attuale
ComponentMetaData.FireError(RUNTIME_ERROR_MODEL_INVALID, ComponentMetaData.Name, "The component is unable to locate the column named " + e.OutputColName + " inside the component metadata. Please review the component.", null, 0, out cancel);
return;
}
}
_inputColIndex = BufferManager.FindColumnByLineageID(ComponentMetaData.InputCollection[0].Buffer, ComponentMetaData.InputCollection[0].InputColumnCollection[GetModel().InputColumnName].LineageID);
// Check if ww should take care of date parsing
if (!m.ParseDates)
{
_dateParsePolicy = DateParseHandling.None;
}
}
