Output the System.Object variable value to a flat file in SSIS

Question

I am sorry if this question is a repeat. I have a system.object variable where I store results for a select query. I need to output the results to a flat file to further process it. I have the following piece of code that works for couple of seconds and then throws the system invocation error. Can you please suggest any edits to this or if I am doing something wrong:

 Public Sub Main()
    Dim x As New OleDb.OleDbDataAdapter
    Dim dt As New DataTable
    Dim str As String = vbNullString
    If System.IO.File.Exists("D:\BKP\AD.txt") = False Then
        System.IO.File.Create("D:\BKP\AD.txt")
    End If
    'MessageBox.Show("Hello")

    Dim i As Int32

    x.Fill(dt, Dts.Variables("User::LDAPResultSet").Value)
    i = dt.Rows.Count

    For j As Int32 = 0 To i - 1

        str = str & Join(dt.Rows.Item(j).ItemArray(), ",") & vbCrLf

    Next
    Dim objWriter As New System.IO.StreamWriter("D:\BKP\AD.txt")
    objWriter.Write(str)
    objWriter.Close()
End Sub
End Class

Is there a better way to write this or if there's an alternative code piece I'd like to try that as well. Thank you for your time.

score 2 · Answer 1 · answered Jun 16 '16 at 18:21

The points from my comment:

You don't need to create the file: the StreamWriter will do that if required, otherwise it will overwrite an existing file.
String.Join(separator, values) - you have the separator as the second argument.
You should also call objWriter.Dispose() when you've finished using it.

But:

A StringBuilder is more efficient for creating a large string.
You can write all the text in one go by using the File.WriteAllText method.

Sub Main()
    Dim outputFile As String = "D:\BKP\AD.txt"

    Dim x As New OleDb.OleDbDataAdapter
    Dim dt As New DataTable
    Dim sb As New Text.StringBuilder

    x.Fill(dt, Dts.Variables("User::LDAPResultSet").Value)

    For j As Integer = 0 To dt.Rows.Count - 1
        sb.AppendLine(String.Join(",", dt.Rows.Item(j).ItemArray()))
    Next

    IO.File.WriteAllText(outputFile, sb.ToString())

End Sub

I guess that you left some lines to do with the OleDbDataAdapter out, but I am not familiar with SSIS.

If you can, use Option Strict On - it would have pointed out the problem with the String.Join for you.

Joe C · Accepted Answer · 2016-06-16T18:34:19.453

I have done it this way in the past:

https://www.timmitchell.net/post/2015/04/20/using-the-ssis-object-variable-as-a-data-flow-source/

Basically pass the variable into a Script Transformation and then add data to the pipeline. From that point you can use a destination component as normal and avoid creating the output file and delimiting the fields.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

// Add in the appropriate namespaces
using System.Data;
using System.Data.OleDb;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    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.vResults);

        // Since we know the column metadata at design time, we simply need to iterate over each row in
        //  the DataTable, creating a new row in our Data Flow buffer for each
        foreach (DataRow dr in dt.Rows)
        {
            // Create a new, empty row in the output buffer
            SalesOutputBuffer.AddRow();

            // Now populate the columns
            SalesOutputBuffer.SalesOrderID = int.Parse(dr["SalesOrderID"].ToString());
            SalesOutputBuffer.RevisionNumber = int.Parse(dr["RevisionNumber"].ToString());
            SalesOutputBuffer.OrderDate = DateTime.Parse(dr["OrderDate"].ToString());
            SalesOutputBuffer.ShipDate = DateTime.Parse(dr["ShipDate"].ToString());
            SalesOutputBuffer.Status = int.Parse(dr["Status"].ToString());
            SalesOutputBuffer.TotalDue = decimal.Parse(dr["TotalDue"].ToString());
        } 
    }
}

I am trying to follow this, but when I run the script I get error saying Column 'ColumnName' does not belong to table. Where am I going wrong? — rvphx, Jun 16 '16 at 19:24
@rvphx check your recordset destination and see the exact column name. could be the case that you have misspelled something. — marko982, Jan 31 '20 at 17:26

score 1 · Answer 3 · answered Jun 16 '16 at 21:54

This is what worked for me finally:

    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.LDAPResultSet);

    // Since we know the column metadata at design time, we simply need to iterate over each row in
    //  the DataTable, creating a new row in our Data Flow buffer for each
    foreach (DataRow dr in dt.Rows)
        //'foreach (DataColumn col in dt.Columns)
        {
            {
                // Create a new, empty row in the output buffer
                LDAPOutputBuffer.AddRow();
                object[] array = dr.ItemArray;
                LDAPOutputBuffer.ADENTName = array[1].ToString();
                LDAPOutputBuffer.DisplayName = array[3].ToString();
                LDAPOutputBuffer.DNName = array[2].ToString();
                LDAPOutputBuffer.Mail = array[0].ToString();
                               }
        }
}

}

Output the System.Object variable value to a flat file in SSIS

3 Answers3