public override void ProcessInput(int inputID, PipelineBuffer inputBuffer)
{
base.ProcessInput(inputID, inputBuffer);
while (inputBuffer.NextRow())
{
var inputString = inputBuffer.GetString(0);
var order = new Order()
{
OrderCode = inputString,
Product = new Product()
{
Name = inputString + "Product"
}
};
var outputXml = XmlSerializer.XmlSerialize(order);
var outputBytes = Encoding.UTF8.GetBytes(outputXml);
outputBuffer.AddRow();
outputBuffer.AddBlobData(0, outputBytes);
}
if (inputBuffer.EndOfRowset)
{
outputBuffer.SetEndOfRowset();
}
}
private void AddOutputRow(ref PipelineBuffer inputbuffer)
{
// For some RESON I STILL DO NOT UNDERSTAND, THIS METHOD FALLS in a sort of race condition.
// The following is the worst workaroud ever, but needed ito quickly address the problem.
while (true)
{
try
{
_outputBuffer.AddRow();
break;
}
catch (Exception e)
{
ComponentMetaData.FireWarning(ComponentConstants.RUNTIME_GENERIC_ERROR, ComponentMetaData.Name, "Outputrow was not ready. ", null, 0);
throw e;
}
}
// Copy the inputs into outputs
for (var i = 0; i < _startOfJsonColIndex; i++)
{
if (inputbuffer[i] is BlobColumn)
{
_outputBuffer.AddBlobData(i, inputbuffer.GetBlobData(i, 0, (int)inputbuffer.GetBlobLength(i)));
}
else
{
_outputBuffer[i] = inputbuffer[i];
}
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
base.ProcessInput(inputID, buffer);
while (buffer.NextRow())
{
var firstColumn = buffer.GetString(0);
buffer.SetString(1, firstColumn + " OK");
buffer.AddBlobData(0, new byte[] { 56, 67 });
}
}
public override void transform(ref PipelineBuffer buffer, int defaultOutputId, int inputColumnBufferIndex, int outputColumnBufferIndex)
{
//Get WKT from buffer
String wkt = buffer.GetString(inputColumnBufferIndex);
Geometry geom = Geometry.CreateFromWkt(wkt);
byte[] geomBytes = new byte[geom.WkbSize()];
geom.ExportToWkb(geomBytes);
buffer.AddBlobData(outputColumnBufferIndex, geomBytes);
buffer.DirectRow(defaultOutputId);
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
base.ProcessInput(inputID, buffer);
while (buffer.NextRow())
{
var firstColumn = buffer.GetString(0);
buffer.SetString(1, firstColumn + " OK");
buffer.AddBlobData(0, new byte[] { 56, 67 });
}
}
public override void transform(ref PipelineBuffer buffer, int defaultOutputId, int inputColumnBufferIndex, int outputColumnBufferIndex)
{
//Get WKT from buffer
String wkt = buffer.GetString(inputColumnBufferIndex);
Geometry geom = Geometry.CreateFromWkt(wkt);
byte[] geomBytes = new byte[geom.WkbSize()];
geom.ExportToWkb(geomBytes);
buffer.AddBlobData(outputColumnBufferIndex, geomBytes);
buffer.DirectRow(defaultOutputId);
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
var geoCodingService = new GeoCodingService();
var output = ComponentMetaData.OutputCollection["Output"];
while (buffer.NextRow())
{
bool modified = false;
string address = GetFullAddressFromBuffer(buffer);
if (!string.IsNullOrWhiteSpace(address))
{
var coordinates = geoCodingService.GeoCodeAddress(address);
if (coordinates != null)
{
if (_columnIndices.ContainsKey(LatitudeColumnName))
{
buffer.SetDecimal(_columnIndices[LatitudeColumnName], coordinates.Latitude);
modified = true;
}
if (_columnIndices.ContainsKey(LongitudeColumnName))
{
buffer.SetDecimal(_columnIndices[LongitudeColumnName], coordinates.Longitude);
modified = true;
}
if (_columnIndices.ContainsKey(LocationColumnName))
{
var pointTaggedText = new SqlChars(string.Format("POINT({0} {1})", coordinates.Longitude, coordinates.Latitude));
var sqlGeography = SqlGeography.STPointFromText(pointTaggedText, 4326);
_tmpMemoryStream.SetLength(0);
sqlGeography.Write(_tmpBinaryWriter);
_tmpBinaryWriter.Flush();
buffer.AddBlobData(_columnIndices[LocationColumnName], _tmpMemoryStream.GetBuffer(), (int)_tmpMemoryStream.Length);
modified = true;
}
}
}
if (modified)
{
// send buffered row to output
buffer.DirectRow(output.ID);
}
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
while (buffer.NextRow())
{
foreach (LocalColumnInfo columnInfo in this.inputColumns)
{
if (columnInfo.IsBlob())
{
SqlGeometry geometry = GetGeometryData(columnInfo.BufferIndex, buffer);
TransformGeometryBuilder builder = new TransformGeometryBuilder(columnInfo.Trans);
geometry.Populate(builder);
buffer.ResetBlobData(columnInfo.BufferIndex);
buffer.AddBlobData(columnInfo.BufferIndex, builder.ConstructedGeometry.STAsBinary().Value);
}
}
}
}
public override void transform(ref PipelineBuffer buffer, int defaultOutputId, int inputColumnBufferIndex, int outputColumnBufferIndex)
{
//Get OGR Geometry from buffer
byte[] geomBytes = new byte[buffer.GetBlobLength(inputColumnBufferIndex)];
geomBytes = buffer.GetBlobData(inputColumnBufferIndex, 0, geomBytes.Length);
Geometry geom = Geometry.CreateFromWkb(geomBytes);
geom = geom.SimplifyPreserveTopology(this.tolerance);
geomBytes = new byte[geom.WkbSize()];
geom.ExportToWkb(geomBytes);
buffer.ResetBlobData(inputColumnBufferIndex);
buffer.AddBlobData(inputColumnBufferIndex, geomBytes);
//Direct row to default output
buffer.DirectRow(defaultOutputId);
}
public override void transform(ref PipelineBuffer buffer, int defaultOutputId, int inputColumnBufferIndex, int outputColumnBufferIndex)
{
//Get OGR Geometry from buffer
byte[] geomBytes = new byte[buffer.GetBlobLength(inputColumnBufferIndex)];
geomBytes = buffer.GetBlobData(inputColumnBufferIndex, 0, geomBytes.Length);
Geometry geom = Geometry.CreateFromWkb(geomBytes);
geom.FlattenTo2D();
geomBytes = new byte[geom.WkbSize()];
geom.ExportToWkb(geomBytes);
buffer.ResetBlobData(inputColumnBufferIndex);
buffer.AddBlobData(inputColumnBufferIndex, geomBytes);
//Direct row to default output
buffer.DirectRow(defaultOutputId);
}
public override void ProcessInput(int inputID, PipelineBuffer inputBuffer)
{
base.ProcessInput(inputID, inputBuffer);
while (inputBuffer.NextRow())
{
var inputBytes = inputBuffer.GetBlobData(0, 0, (int)inputBuffer.GetBlobLength(0));
var inputXml = Encoding.UTF8.GetString(inputBytes);
var inputObject = XmlSerializer.XmlDeserialize <TSource>(inputXml);
var outputObject = ProcessInput(inputObject);
var outputXml = XmlSerializer.XmlSerialize(outputObject);
var outputBytes = Encoding.UTF8.GetBytes(outputXml);
outputBuffer.AddRow();
outputBuffer.AddBlobData(0, outputBytes);
}
if (inputBuffer.EndOfRowset)
{
outputBuffer.SetEndOfRowset();
}
}
public override void transform(ref PipelineBuffer buffer, int defaultOutputId, int inputColumnBufferIndex, int outputColumnBufferIndex)
{
//Get OGR Geometry from buffer
byte[] geomBytes = new byte[buffer.GetBlobLength(inputColumnBufferIndex)];
geomBytes = buffer.GetBlobData(inputColumnBufferIndex, 0, geomBytes.Length);
Geometry geom = Geometry.CreateFromWkb(geomBytes);
if (this.isMultipart(geom))
{
for (int i = 0; i < geom.GetGeometryCount(); i++)
{
Geometry geomPart = geom.GetGeometryRef(i);
geomBytes = new byte[geom.WkbSize()];
geomPart.ExportToWkb(geomBytes);
buffer.ResetBlobData(inputColumnBufferIndex);
buffer.AddBlobData(inputColumnBufferIndex, geomBytes);
buffer.DirectRow(defaultOutputId);
}
}
else
{
buffer.DirectRow(defaultOutputId);
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
while (buffer.NextRow())
{
foreach (LocalColumnInfo columnInfo in this.inputColumns)
{
if (columnInfo.IsBlob())
{
SqlGeometry geometry = GetGeometryData(columnInfo.BufferIndex, buffer);
TransformGeometryBuilder builder = new TransformGeometryBuilder(columnInfo.Trans);
geometry.Populate(builder);
buffer.ResetBlobData(columnInfo.BufferIndex);
buffer.AddBlobData(columnInfo.BufferIndex, builder.ConstructedGeometry.STAsBinary().Value);
}
}
}
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
//identify buffers
PipelineBuffer errorBuffer = null;
PipelineBuffer defaultBuffer = null;
for (int x = 0; x < outputs; x++)
{
if (outputIDs[x] == errorOutputID)
{
errorBuffer = buffers[x];
}
else
{
defaultBuffer = buffers[x];
}
}
//get ogrlayer and ogrlayer feature definition
Layer OGRLayer;
bool isSQLLayer = (!(ComponentMetaData.CustomPropertyCollection["SQL Statement"].Value == null || ComponentMetaData.CustomPropertyCollection["SQL Statement"].Value.ToString() == string.Empty));
if (isSQLLayer)
{
OGRLayer = getSQLLayer();
}
else
{
OGRLayer = getLayer();
}
Feature OGRFeature;
FeatureDefn OGRFeatureDef = OGRLayer.GetLayerDefn();
//initialize columnInfo object
columnInfo ci = new columnInfo();
//for each row in ogrlayer add row to output buffer
while ((OGRFeature = OGRLayer.GetNextFeature()) != null)
{
try
{
defaultBuffer.AddRow();
//set buffer column values
for (int i = 0; i < this.columnInformation.Count; i++)
{
ci = (columnInfo)this.columnInformation[i];
if (ci.geom)
{
Geometry geom = OGRFeature.GetGeometryRef();
if (geom != null)
{
byte[] geomBytes = new byte[geom.WkbSize()];
geom.ExportToWkb(geomBytes);
defaultBuffer.AddBlobData(ci.bufferColumnIndex, geomBytes);
}
}
else
{
int OGRFieldIndex = OGRFeatureDef.GetFieldIndex(ci.columnName);
FieldDefn OGRFieldDef = OGRFeatureDef.GetFieldDefn(OGRFieldIndex);
FieldType OGRFieldType = OGRFieldDef.GetFieldType();
//declare datetime variables
int pnYear, pnMonth, pnDay, pnHour, pnMinute, pnTZFlag;
float pnSecond;
DateTime dt;
TimeSpan ts;
switch (OGRFieldType)
{
//case FieldType.OFTBinary:
// break;
case FieldType.OFTDate:
OGRFeature.GetFieldAsDateTime(OGRFieldIndex, out pnYear, out pnMonth, out pnDay, out pnHour, out pnMinute, out pnSecond, out pnTZFlag);
dt = new DateTime(pnYear, pnMonth, pnDay);
defaultBuffer.SetDate(ci.bufferColumnIndex, dt);
break;
case FieldType.OFTDateTime:
OGRFeature.GetFieldAsDateTime(OGRFieldIndex, out pnYear, out pnMonth, out pnDay, out pnHour, out pnMinute, out pnSecond, out pnTZFlag);
dt = new DateTime(pnYear, pnMonth, pnDay, pnHour, pnMinute, (int)pnSecond);
//set time zone?
defaultBuffer.SetDateTime(ci.bufferColumnIndex, dt);
break;
case FieldType.OFTInteger:
defaultBuffer.SetInt32(ci.bufferColumnIndex, OGRFeature.GetFieldAsInteger(OGRFieldIndex));
break;
case FieldType.OFTInteger64:
defaultBuffer.SetInt64(ci.bufferColumnIndex, OGRFeature.GetFieldAsInteger(OGRFieldIndex));
break;
case FieldType.OFTReal:
defaultBuffer.SetDouble(ci.bufferColumnIndex, OGRFeature.GetFieldAsDouble(OGRFieldIndex));
break;
case FieldType.OFTTime:
OGRFeature.GetFieldAsDateTime(OGRFieldIndex, out pnYear, out pnMonth, out pnDay, out pnHour, out pnMinute, out pnSecond, out pnTZFlag);
ts = new TimeSpan(pnHour, pnMinute, (int)pnSecond);
defaultBuffer.SetTime(ci.bufferColumnIndex, ts);
break;
case FieldType.OFTString:
default:
defaultBuffer.SetString(ci.bufferColumnIndex, OGRFeature.GetFieldAsString(OGRFieldIndex));
break;
}
}
}
}
catch (Exception ex)
{
//redirect to error buffer
if (ci.errorDisposition == DTSRowDisposition.RD_RedirectRow)
{
// Add a row to the error buffer.
errorBuffer.AddRow();
// Set the error information.
int errorCode = System.Runtime.InteropServices.Marshal.GetHRForException(ex);
errorBuffer.SetErrorInfo(errorOutputID, errorCode, ci.lineageID);
// Remove the row that was added to the default buffer.
defaultBuffer.RemoveRow();
}
//fail component
else if (ci.errorDisposition == DTSRowDisposition.RD_FailComponent || ci.errorDisposition == DTSRowDisposition.RD_NotUsed)
{
ComponentMetaData.FireError(0, "primeoutput failure", ex.ToString(), string.Empty, 0, out cancel);
throw;
}
}
}
//set end of rowset for buffers
if (defaultBuffer != null)
{
defaultBuffer.SetEndOfRowset();
}
if (errorBuffer != null)
{
errorBuffer.SetEndOfRowset();
}
//clean up layer object
if (isSQLLayer)
{
this.OGRDataSource.ReleaseResultSet(OGRLayer);
}
}
private static void SetBufferValue(PipelineBuffer outputBuffer, int outputBufferIndex, object value, DataType dataType)
{
if ((object)outputBuffer == null)
throw new ArgumentNullException("outputBuffer");
if ((object)value == null)
outputBuffer.SetNull(outputBufferIndex);
else
{
switch (dataType)
{
case DataType.DT_BOOL:
outputBuffer.SetBoolean(outputBufferIndex, Convert.ToBoolean(value));
break;
case DataType.DT_BYTES:
outputBuffer.SetBytes(outputBufferIndex, (byte[])value);
break;
case DataType.DT_CY:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_DATE:
outputBuffer.SetDateTime(outputBufferIndex, Convert.ToDateTime(value));
break;
case DataType.DT_DBDATE:
outputBuffer[outputBufferIndex] = (DateTime)value;
break;
case DataType.DT_DBTIME:
// it is not possible to populate DT_DBTIME columns from managed code in SSIS 2005
break;
case DataType.DT_DBTIMESTAMP:
outputBuffer.SetDateTime(outputBufferIndex, Convert.ToDateTime(value));
break;
case DataType.DT_DECIMAL:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_FILETIME:
outputBuffer[outputBufferIndex] = value;
break;
case DataType.DT_GUID:
outputBuffer.SetGuid(outputBufferIndex, (Guid)value);
break;
case DataType.DT_I1:
outputBuffer.SetSByte(outputBufferIndex, Convert.ToSByte(value));
break;
case DataType.DT_I2:
outputBuffer.SetInt16(outputBufferIndex, Convert.ToInt16(value));
break;
case DataType.DT_I4:
outputBuffer.SetInt32(outputBufferIndex, Convert.ToInt32(value));
break;
case DataType.DT_I8:
outputBuffer.SetInt64(outputBufferIndex, Convert.ToInt64(value));
break;
case DataType.DT_IMAGE:
// we have to treat blob columns a little differently
BlobColumn colDT_IMAGE = (BlobColumn)value;
if (colDT_IMAGE.IsNull)
outputBuffer.SetNull(outputBufferIndex);
else
outputBuffer.AddBlobData(outputBufferIndex, colDT_IMAGE.GetBlobData(0, (int)colDT_IMAGE.Length));
break;
case DataType.DT_NTEXT:
// we have to treat blob columns a little differently
BlobColumn colDT_NTEXT = (BlobColumn)value;
if (colDT_NTEXT.IsNull)
outputBuffer.SetNull(outputBufferIndex);
else
outputBuffer.AddBlobData(outputBufferIndex, colDT_NTEXT.GetBlobData(0, (int)colDT_NTEXT.Length));
break;
case DataType.DT_NULL:
outputBuffer.SetNull(outputBufferIndex);
break;
case DataType.DT_NUMERIC:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_R4:
outputBuffer.SetSingle(outputBufferIndex, Convert.ToSingle(value));
break;
case DataType.DT_R8:
outputBuffer.SetDouble(outputBufferIndex, Convert.ToDouble(value));
break;
case DataType.DT_STR:
outputBuffer.SetString(outputBufferIndex, value.ToString());
break;
case DataType.DT_TEXT:
// we have to treat blob columns a little differently
BlobColumn colDT_TEXT = (BlobColumn)value;
if (colDT_TEXT.IsNull)
outputBuffer.SetNull(outputBufferIndex);
else
outputBuffer.AddBlobData(outputBufferIndex, colDT_TEXT.GetBlobData(0, (int)colDT_TEXT.Length));
break;
case DataType.DT_UI1:
outputBuffer.SetByte(outputBufferIndex, Convert.ToByte(value));
break;
case DataType.DT_UI2:
outputBuffer.SetUInt16(outputBufferIndex, Convert.ToUInt16(value));
break;
case DataType.DT_UI4:
outputBuffer.SetUInt32(outputBufferIndex, Convert.ToUInt32(value));
break;
case DataType.DT_UI8:
outputBuffer.SetUInt64(outputBufferIndex, Convert.ToUInt64(value));
break;
case DataType.DT_WSTR:
outputBuffer.SetString(outputBufferIndex, value.ToString());
break;
default:
throw new InvalidOperationException(string.Format("Ah snap."));
}
}
}
private static void SetBufferValue(PipelineBuffer outputBuffer, int outputBufferIndex, object value, DataType dataType)
{
if ((object)outputBuffer == null)
{
throw new ArgumentNullException("outputBuffer");
}
if ((object)value == null)
{
outputBuffer.SetNull(outputBufferIndex);
}
else
{
switch (dataType)
{
case DataType.DT_BOOL:
outputBuffer.SetBoolean(outputBufferIndex, Convert.ToBoolean(value));
break;
case DataType.DT_BYTES:
outputBuffer.SetBytes(outputBufferIndex, (byte[])value);
break;
case DataType.DT_CY:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_DATE:
outputBuffer.SetDateTime(outputBufferIndex, Convert.ToDateTime(value));
break;
case DataType.DT_DBDATE:
outputBuffer[outputBufferIndex] = (DateTime)value;
break;
case DataType.DT_DBTIME:
// it is not possible to populate DT_DBTIME columns from managed code in SSIS 2005
break;
case DataType.DT_DBTIMESTAMP:
outputBuffer.SetDateTime(outputBufferIndex, Convert.ToDateTime(value));
break;
case DataType.DT_DECIMAL:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_FILETIME:
outputBuffer[outputBufferIndex] = value;
break;
case DataType.DT_GUID:
outputBuffer.SetGuid(outputBufferIndex, (Guid)value);
break;
case DataType.DT_I1:
outputBuffer.SetSByte(outputBufferIndex, Convert.ToSByte(value));
break;
case DataType.DT_I2:
outputBuffer.SetInt16(outputBufferIndex, Convert.ToInt16(value));
break;
case DataType.DT_I4:
outputBuffer.SetInt32(outputBufferIndex, Convert.ToInt32(value));
break;
case DataType.DT_I8:
outputBuffer.SetInt64(outputBufferIndex, Convert.ToInt64(value));
break;
case DataType.DT_IMAGE:
// we have to treat blob columns a little differently
BlobColumn colDT_IMAGE = (BlobColumn)value;
if (colDT_IMAGE.IsNull)
{
outputBuffer.SetNull(outputBufferIndex);
}
else
{
outputBuffer.AddBlobData(outputBufferIndex, colDT_IMAGE.GetBlobData(0, (int)colDT_IMAGE.Length));
}
break;
case DataType.DT_NTEXT:
// we have to treat blob columns a little differently
BlobColumn colDT_NTEXT = (BlobColumn)value;
if (colDT_NTEXT.IsNull)
{
outputBuffer.SetNull(outputBufferIndex);
}
else
{
outputBuffer.AddBlobData(outputBufferIndex, colDT_NTEXT.GetBlobData(0, (int)colDT_NTEXT.Length));
}
break;
case DataType.DT_NULL:
outputBuffer.SetNull(outputBufferIndex);
break;
case DataType.DT_NUMERIC:
outputBuffer.SetDecimal(outputBufferIndex, Convert.ToDecimal(value));
break;
case DataType.DT_R4:
outputBuffer.SetSingle(outputBufferIndex, Convert.ToSingle(value));
break;
case DataType.DT_R8:
outputBuffer.SetDouble(outputBufferIndex, Convert.ToDouble(value));
break;
case DataType.DT_STR:
outputBuffer.SetString(outputBufferIndex, value.ToString());
break;
case DataType.DT_TEXT:
// we have to treat blob columns a little differently
BlobColumn colDT_TEXT = (BlobColumn)value;
if (colDT_TEXT.IsNull)
{
outputBuffer.SetNull(outputBufferIndex);
}
else
{
outputBuffer.AddBlobData(outputBufferIndex, colDT_TEXT.GetBlobData(0, (int)colDT_TEXT.Length));
}
break;
case DataType.DT_UI1:
outputBuffer.SetByte(outputBufferIndex, Convert.ToByte(value));
break;
case DataType.DT_UI2:
outputBuffer.SetUInt16(outputBufferIndex, Convert.ToUInt16(value));
break;
case DataType.DT_UI4:
outputBuffer.SetUInt32(outputBufferIndex, Convert.ToUInt32(value));
break;
case DataType.DT_UI8:
outputBuffer.SetUInt64(outputBufferIndex, Convert.ToUInt64(value));
break;
case DataType.DT_WSTR:
outputBuffer.SetString(outputBufferIndex, value.ToString());
break;
default:
throw new InvalidOperationException(string.Format("Ah snap."));
}
}
}
/// <summary>
/// Processes rows in the inpuyt buffer
/// </summary>
/// <param name="inputID">ID of the input to process the input rows</param>
/// <param name="buffer">Pipeline bufere with rows to process</param>
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
rowsProcessed++;
if (outputColumns.Count == 0)
{
continue;
}
XmlColumn lastCol = null;
XElement rowElement = null;
foreach (XmlColumn outCol in outputColumns)
{
if (rowElement == null || lastCol == null || lastCol.SerializeLineage != outCol.SerializeLineage || lastCol.SerializeDataType != outCol.SerializeDataType)
{
rowElement = new XElement("row");
//Add sourceID and sourceNme attributes to the row node if those are specified in the component properties
if (!string.IsNullOrEmpty(outCol.SourceID))
{
rowElement.Add(new XAttribute("sourceID", outCol.SourceID));
}
if (!string.IsNullOrEmpty(outCol.SourceName))
{
rowElement.Add(new XAttribute("sourceName", outCol.SourceName));
}
XElement columnElement = null;
//Process XML for selected input columns
for (int i = 0; i < outCol.XmlInputColumns.Count; i++)
{
int colIdx = outCol.XmlInputColumns[i];
InputBufferColumnInfo bci = inputBufferColumns[colIdx];
BufferColumn col = buffer.GetColumnInfo(bci.Index);
byte[] bdata;
columnElement = new XElement("Column");
//add name, id and lineageId atributes to the column node
columnElement.Add(new XAttribute("name", bci.Name));
if (outCol.SerializeLineage) //Serialize ID and LineageId
{
columnElement.Add(new XAttribute("id", bci.ID), new XAttribute("lineageId", bci.LineageID));
}
if (outCol.SerializeDataType) //Serialize Data Type Information
{
columnElement.Add(
new XAttribute("dataType", bci.DataType.ToString())
, new XAttribute("length", bci.Length)
, new XAttribute("precision", bci.Precision)
, new XAttribute("scale", bci.Scale)
);
}
//if column value is null add isNull attribute to the column node
if (buffer.IsNull(bci.Index))
{
columnElement.Add(new XAttribute("isNull", true));
}
else //get data for the column and store them as data of the column node
{
string colData = string.Empty;
switch (col.DataType)
{
case DataType.DT_BYTES:
bdata = buffer.GetBytes(bci.Index);
colData = BytesToHexString(bdata); //convert binary data to a hexadecimal string
break;
case DataType.DT_IMAGE:
bdata = buffer.GetBlobData(bci.Index, 0, (int)buffer.GetBlobLength(bci.Index));
colData = BytesToHexString(bdata); //convert binary data to a hexadecimal string
break;
case DataType.DT_NTEXT:
bdata = buffer.GetBlobData(bci.Index, 0, (int)buffer.GetBlobLength(bci.Index));
colData = Encoding.Unicode.GetString(bdata);
break;
case DataType.DT_TEXT:
bdata = buffer.GetBlobData(bci.Index, 0, (int)buffer.GetBlobLength(bci.Index));
colData = Encoding.GetEncoding(col.CodePage).GetString(bdata);
break;
default:
colData = Convert.ToString(buffer[bci.Index], CultureInfo.InvariantCulture);
break;
}
columnElement.SetValue(colData);
rowElement.Add(columnElement);
}
}
}
if (outCol.DataType == DataType.DT_WSTR)
{
string str = rowElement.ToString(outCol.SaveOptions);
if (str.Length > outCol.DataLen)
{
bool cancel = false;
string msg = string.Format("Data Truncation has occured when processing row {0}. Could not write {1} characters into column [{2}] of length {3}", rowsProcessed, str.Length, outCol.Name, outCol.DataLen);
this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, msg, string.Empty, 0, out cancel);
if (cancel)
{
throw new System.Exception(msg);
}
break;
}
buffer.SetString(outCol.Index, str);
}
else
{
buffer.AddBlobData(outCol.Index, Encoding.Unicode.GetBytes(rowElement.ToString(outCol.SaveOptions)));
}
lastCol = outCol;
}
}
}
}
/// <summary>
/// This is where the data is loaded into the output buffer
/// </summary>
/// <param name="outputs"></param>
/// <param name="outputIDs"></param>
/// <param name="buffers"></param>
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
string sharepointUrl = (string)ComponentMetaData.CustomPropertyCollection[C_SHAREPOINTSITEURL].Value;
string sharepointList = (string)ComponentMetaData.CustomPropertyCollection[C_SHAREPOINTLISTNAME].Value;
string sharepointListView = (string)ComponentMetaData.CustomPropertyCollection[C_SHAREPOINTLISTVIEWNAME].Value;
XElement camlQuery = XElement.Parse((string)ComponentMetaData.CustomPropertyCollection[C_CAMLQUERY].Value);
short batchSize = (short)ComponentMetaData.CustomPropertyCollection[C_BATCHSIZE].Value;
Enums.TrueFalseValue isRecursive = (Enums.TrueFalseValue)ComponentMetaData.CustomPropertyCollection[C_ISRECURSIVE].Value;
Enums.TrueFalseValue includeFolders = (Enums.TrueFalseValue)ComponentMetaData.CustomPropertyCollection[C_INCLUDEFOLDERS].Value;
PipelineBuffer outputBuffer = buffers[0];
// Get the field names from the output collection
var fieldNames = (from col in
ComponentMetaData.OutputCollection[0].OutputColumnCollection.Cast <IDTSOutputColumn>()
select(string) col.CustomPropertyCollection[0].Value);
// Load the data from sharepoint
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
timer.Start();
var listData = SharePointUtility.ListServiceUtility.GetListItemData(
new Uri(sharepointUrl), _credentials, sharepointList, sharepointListView, fieldNames, camlQuery,
isRecursive == Enums.TrueFalseValue.True ? true : false, batchSize);
timer.Stop();
bool fireAgain = false;
int actualRowCount = 0;
foreach (var row in listData)
{
// Determine if we should continue based on if this is a folder item or not (filter can be pushed up to CAML if
// perf becomes an issue)
bool canContinue = true;
if ((row.ContainsKey("ContentType")) &&
(row["ContentType"] == "Folder") &&
(includeFolders == Enums.TrueFalseValue.False))
{
canContinue = false;
}
if (canContinue)
{
actualRowCount++;
outputBuffer.AddRow();
foreach (var fieldName in _bufferLookup.Keys)
{
if (row.ContainsKey(fieldName))
{
switch (_bufferLookupDataType[fieldName])
{
case DataType.DT_NTEXT:
outputBuffer.AddBlobData(_bufferLookup[fieldName],
Encoding.Unicode.GetBytes(row[fieldName].ToString()));
break;
case DataType.DT_WSTR:
outputBuffer.SetString(_bufferLookup[fieldName], row[fieldName]);
break;
case DataType.DT_R8:
outputBuffer.SetDouble(_bufferLookup[fieldName], double.Parse(row[fieldName], _culture));
break;
case DataType.DT_I4:
outputBuffer.SetInt32(_bufferLookup[fieldName], int.Parse(row[fieldName], _culture));
break;
case DataType.DT_BOOL:
outputBuffer.SetBoolean(_bufferLookup[fieldName], (int.Parse(row[fieldName], _culture) == 1));
break;
case DataType.DT_GUID:
outputBuffer.SetGuid(_bufferLookup[fieldName], new Guid(row[fieldName]));
break;
case DataType.DT_DBTIMESTAMP:
outputBuffer.SetDateTime(_bufferLookup[fieldName], DateTime.Parse(row[fieldName], _culture));
break;
}
}
else
{
switch (_bufferLookupDataType[fieldName])
{
case DataType.DT_NTEXT:
outputBuffer.AddBlobData(_bufferLookup[fieldName],
Encoding.Unicode.GetBytes(String.Empty));
break;
case DataType.DT_WSTR:
outputBuffer.SetString(_bufferLookup[fieldName], String.Empty);
break;
case DataType.DT_BOOL:
outputBuffer.SetBoolean(_bufferLookup[fieldName], false);
break;
default:
outputBuffer.SetNull(_bufferLookup[fieldName]);
break;
}
}
}
}
}
string infoMsg = string.Format(CultureInfo.InvariantCulture,
"Loaded {0} records from list '{1}' at '{2}'. Elapsed time is {3}ms",
actualRowCount,
sharepointList,
sharepointUrl,
timer.ElapsedMilliseconds);
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, infoMsg, "", 0, ref fireAgain);
ComponentMetaData.IncrementPipelinePerfCounter(
DTS_PIPELINE_CTR_ROWSREAD, (uint)actualRowCount);
outputBuffer.SetEndOfRowset();
}
public override void ProcessInput(int inputID, PipelineBuffer buffer) {
var geoCodingService = new GeoCodingService();
var output = ComponentMetaData.OutputCollection["Output"];
while (buffer.NextRow())
{
bool modified = false;
string address = GetFullAddressFromBuffer(buffer);
if (!string.IsNullOrWhiteSpace(address))
{
var coordinates = geoCodingService.GeoCodeAddress(address);
if (coordinates != null)
{
if (_columnIndices.ContainsKey(LatitudeColumnName))
{
buffer.SetDecimal(_columnIndices[LatitudeColumnName], coordinates.Latitude);
modified = true;
}
if (_columnIndices.ContainsKey(LongitudeColumnName))
{
buffer.SetDecimal(_columnIndices[LongitudeColumnName], coordinates.Longitude);
modified = true;
}
if (_columnIndices.ContainsKey(LocationColumnName))
{
var pointTaggedText = new SqlChars(string.Format("POINT({0} {1})", coordinates.Longitude, coordinates.Latitude));
var sqlGeography = SqlGeography.STPointFromText(pointTaggedText, 4326);
_tmpMemoryStream.SetLength(0);
sqlGeography.Write(_tmpBinaryWriter);
_tmpBinaryWriter.Flush();
buffer.AddBlobData(_columnIndices[LocationColumnName], _tmpMemoryStream.GetBuffer(), (int)_tmpMemoryStream.Length);
modified = true;
}
}
}
if (modified)
{
// send buffered row to output
buffer.DirectRow(output.ID);
}
}
}
