• Well here it is! Script Task success! I hope this script may help someone else using MYOB Order imports.

    FYI, I am recently new to SSIS and don't claim to be an expert coder but this is what i've managed to get working. Perhaps a more experienced coder can make it better.

    Anyway, to make the script simple just assume my SP returns only 1 column (OrderID) with an index of 0 which is then written to a text file with blank lines inserted between each repeating order #.

    -----------------------------------

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.IO

    Imports System.Data.SqlClient

    Public Class ScriptMain

    Public Sub Main()

    'Set the database connection and run the SP which returns the OrderID column

    Dim connectionstring As String = "Data Source=localhost;Initial Catalog=xxxxxx;Integrated Security=SSPI;"

    Dim con As New SqlConnection(connectionstring)

    Dim cmd As New SqlCommand("sp_XXXXX", con)

    cmd.CommandType = CommandType.StoredProcedure

    con.Open()

    'Read the SQL object into a datareader for looping

    Dim reader As SqlDataReader = cmd.ExecuteReader()

    'Open a Streamwriter instance to write results to the text output file

    Using writer As StreamWriter = New StreamWriter("C:\temp\file.txt")

    Dim LastOrderNumber As String

    'Read the first line of the dataset

    reader.Read()

    'Initialise the variable to store the first orderID from the first row. This will only start to be used for comparison from the 2nd line onwards in the loop below.

    LastOrderNumber = reader.GetString(0)

    'Write the first OrderID from the first row outside the loop. Assumed you will always have one row of data.

    writer.Write(reader.GetString(0))

    'Start reading the 2nd line onwards in the dataset

    While reader.Read

    'Compare new row's orderID to previous row's orderID and write line if the same.

    If reader.GetString(0) = LastOrderNumber Then

    writer.Write(reader.GetString(0))

    'Update the variable with the latest OrderID for use in the next loop.

    LastOrderNumber = reader.GetString(0)

    'If new row's OrderID is different then write a blank line before the new row data to indicate a new Order.

    Else

    writer.WriteLine()

    writer.Write(reader.GetString(0))

    ''Update the variable with the latest OrderID for use in the next loop.

    LastOrderNumber = reader.GetString(0)

    End If

    End While

    reader.Close()

    writer.Flush()

    writer.Close()

    con.Close()

    End Using

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class