/// <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); }
private void ResolveComponentCustomProperties() { _dataProfileFileName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_FILE_NAME_PROPERTY_NAME].Value.ToString(); if (VariableDispenser.Contains(_dataProfileFileName)) { IDTSVariables100 variables = null; VariableDispenser.LockOneForRead(_dataProfileFileName, ref variables); _dataProfileFileName = (String)variables[0].Value; } _dataProfileColumnName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_COLUMN_NAME_PROPERTY_NAME].Value.ToString(); if (VariableDispenser.Contains(_dataProfileColumnName)) { IDTSVariables100 variables = null; VariableDispenser.LockOneForRead(_dataProfileColumnName, ref variables); _dataProfileColumnName = (String)variables[0].Value; } _regexPatterns.Clear(); _regexPatterns = this.LoadRegularExpressions(_dataProfileFileName, _dataProfileColumnName); _emailAddressInputColumnName = ComponentMetaData.InputCollection[INPUT_NAME].CustomPropertyCollection[INPUT_COLUMN_NAME].Value.ToString(); // v2 _regexOptionsNumber = (Int64)(ComponentMetaData.CustomPropertyCollection[REGEX_OPTIONS_PROPERTY_NAME].Value); }
/// <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); }
//public override void AcquireConnections(object transaction) //{ // IDTSRuntimeConnection100 item; // object obj; // bool flag, flag1, flag2, flag3; // try // { // item = base.ComponentMetaData.RuntimeConnectionCollection[CONNNAME]; // } // catch (Exception ex) // { // Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport = base.ErrorSupport; // object[] objArray = new object[] { CONNNAME }; // errorSupport.FireErrorWithArgs(-1071611878, out flag1, objArray); // throw new PipelineComponentHResultException(-1071611878); // } // IDTSConnectionManager100 connectionManager = item.ConnectionManager; // if (connectionManager == null) // { // Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport1 = base.ErrorSupport; // object[] objArray1 = new object[] { CONNNAME }; // errorSupport1.FireErrorWithArgs(-1071611851, out flag2, objArray1); // throw new PipelineComponentHResultException(-1071611851); // } // try // { // obj = connectionManager.AcquireConnection(transaction); // } // catch (Exception ex2) // { // Exception ex1 = ex2; // var errorSupport2 = base.ErrorSupport; // object[] connectionManagerId = new object[] { item.ConnectionManagerID, ex1.Message }; // errorSupport2.FireErrorWithArgs(-1071610798, out flag3, connectionManagerId); // throw new PipelineComponentHResultException(ex1.Message, -1071610798); // } // var httpConnection = obj as HttpClientConnection100; // //base.AcquireConnections(transaction); //} public override void PreExecute() { IDTSInput100 input = ComponentMetaData.InputCollection[0]; bufferIdxs = new int[input.InputColumnCollection.Count]; columnDictionary = new BufferNameMap(); for (int x = 0; x < input.InputColumnCollection.Count; x++) { IDTSInputColumn100 column = input.InputColumnCollection[x]; bufferIdxs[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID); columnDictionary.Add(column.Name, bufferIdxs[x]); } //base.PreExecute(); IDTSVariables100 variables = null; VariableDispenser.LockForRead(ADMINKEY); VariableDispenser.LockForRead(APPID); VariableDispenser.LockForRead(ZUMONAME); VariableDispenser.GetVariables(out variables); object adminKeyObj = variables[0].Value; object appIdObj = variables[1].Value; object zumoNameObj = variables[2].Value; this.adminKey = adminKeyObj.ToString(); this.appId = appIdObj.ToString(); this.zumoName = zumoNameObj.ToString(); //this.dataTable = new DataTable() //{ // Locale = CultureInfo.InvariantCulture //}; //IDTSInput100 dTSInput = base.ComponentMetaData.InputCollection[0]; //IDTSExternalMetadataColumnCollection100 externalColumnCollection = dTSInput.ExternalMetadataColumnCollection; //IDTSInputColumnCollection100 inputColumnCollection = dTSInput.InputColumnCollection; //int count = inputColumnCollection.Count; //this.tableColumns = new DataColumn[count]; //IDTSExternalMetadataColumn100[] externalMetadataColumnArray = new IDTSExternalMetadataColumn100[count]; //this.bufferIdxs = new int[count]; //for (int i = 0; i < count; i++) //{ // IDTSInputColumn100 inputColumn = inputColumnCollection[i]; // IDTSExternalMetadataColumn100 externalMetadataColumn = externalColumnCollection.FindObjectByID(inputColumn.ExternalMetadataColumnID); // externalMetadataColumnArray[i] = externalMetadataColumn; // DataType dataType = inputColumn.DataType; // bool flag5 = false; // dataType = PipelineComponent.ConvertBufferDataTypeToFitManaged(dataType, ref flag5); // Type dataRecordType = PipelineComponent.BufferTypeToDataRecordType(dataType); // this.tableColumns[i] = new DataColumn(externalMetadataColumn.Name, dataRecordType); // int lineageId = inputColumn.LineageID; // try // { // this.bufferIdxs[i] = base.BufferManager.FindColumnByLineageID(dTSInput.Buffer, lineageId); // } // catch (Exception ex) // { // Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport2 = base.ErrorSupport; // object[] name = new object[] { lineageId, externalMetadataColumn.Name }; // errorSupport2.FireErrorWithArgs(-1071610795, out flag4, name); // throw new PipelineComponentHResultException(-1071610795); // } //} //this.dataTable.Columns.AddRange(this.tableColumns); IDTSCustomProperty100 item = base.ComponentMetaData.CustomPropertyCollection["TableName"]; string str; if (item.Value == null) { str = null; } else { str = item.Value.ToString().Trim(); } this.tableName = str; switch (this.tableName) { case "charges": chargesServiceList = GetAllCharges(); ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} charges from the online service.", chargesServiceList.Count), "", 0, ref pbCancel); chargesToSave = new List <Charge>(); break; case "holds": holdsServiceList = GetAllHolds(); ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} holds from the online service.", holdsServiceList.Count), "", 0, ref pbCancel); holdsToSave = new List <Hold>(); break; case "inmates": default: try { inmatesServiceList = GetAllInmates(); } catch (Exception) { RetryGetAllInmates(); } ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} inmates from the online service.", inmatesServiceList.Count), "", 0, ref pbCancel); inmatesToSave = new List <Inmate>(); break; } }
// 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; } }