Last active
December 4, 2019 10:49
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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