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