Skip to content

Instantly share code, notes, and snippets.

@janis-veinbergs
Last active December 4, 2019 10:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save janis-veinbergs/df95b0198c972fbd42e2dc7b7eb70153 to your computer and use it in GitHub Desktop.
Save janis-veinbergs/df95b0198c972fbd42e2dc7b7eb70153 to your computer and use it in GitHub Desktop.
Use Script Task to output data within object variable as rows. You still have to define output columns.
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Text;
//Not using any syntax after .net 4.5 as won't be debuggable until 3.2 Preview https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPoint]
public class ScriptMain : UserComponent {
/// <summary>
/// The output column names and data types for this component should match to those with that saved object.
/// </summary>
public override void CreateNewOutputRows() {
// Set up the DataAdapter to extract the data, and the DataTable object to capture those results
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
// Extract the data from the object variable into the table
da.Fill(dt, Variables.KayakoRetrievedNoteList);
//Dictionary may help performance if many columns & rows.
Dictionary<string, PropertyInfo> properties = new Dictionary<string, PropertyInfo>(10);
Array.ForEach(
notesBuffer.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance),
p => properties.Add(p.Name, p)
);
foreach (DataRow row in dt.Rows) {
notesBuffer.AddRow();
foreach (DataColumn column in dt.Columns) {
//Sets value only if not null & output column defined
if (properties.ContainsKey(column.ColumnName) && row[column.ColumnName] != null && row[column.ColumnName].GetType() != typeof(DBNull)) {
var property = properties[column.ColumnName];
//Blob column is handled differently
if (property.PropertyType == typeof(BlobColumn)) {
var blobColumn = (BlobColumn)property.GetValue(notesBuffer);
if (blobColumn.ColumnInfo.DataType == DataType.DT_TEXT || blobColumn.ColumnInfo.DataType == DataType.DT_NTEXT) {
var value = row[column.ColumnName] as string;
blobColumn.AddBlobData(
(blobColumn.ColumnInfo.DataType == DataType.DT_TEXT ? Encoding.GetEncoding(blobColumn.ColumnInfo.CodePage) : Encoding.Unicode)
.GetBytes(value)
);
}
} else {
property.SetValue(notesBuffer, row[column.ColumnName]);
}
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment