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