rvphx rvphx - 1 year ago 87
Vb.net Question

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

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
End If

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

Dim objWriter As New System.IO.StreamWriter("D:\BKP\AD.txt")
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.

Answer Source

I have done it this way in the past:


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;

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

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

            // 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());