Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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