SSIS Export to Flat File with a blank line b/w each Order

  • We have built a simple SSIS package to export all Orders combined with OrderDetails to produce multiple line item for Importing into MYOB accounting package. The SSIS simply runs:

    1) OLEDB SQL Extract (inner join from Orders/OrderDetails)

    2) Flat File destination (text file output in comma delimted)

    BAD data (NO line break between Orders):

    OrderID, ProductID, ItemPrice

    1,1000,50.00

    1,1001,75.00

    2,1000,50.00

    3,1001,75.00

    3,1002,95.00

    3,1003,44.00

    Here is what we want to produce as output in the text file because the MYOB accounting Import recognised new orders by line breaks. How do we achieve this using SQL or SSIS or perhaps a .NET script task??????

    GOOD data:

    OrderID, ProductID, ItemPrice

    1,1000,50.00

    1,1001,75.00

    2,1000,50.00

    3,1001,75.00

    3,1002,95.00

    3,1003,44.00

  • You could use a script task for this. Go ahead and run your existing SSIS package to the the bad file (without blank lines), then create a new script task and use something similar to below.

    Dim origFile as StreamReader = New StreamReader(add_your_orig_filename_here)

    Dim newFile as StreamWriter = new StreamWriter(add_your_new_filename_here)

    ' Store the last order number

    Dim lastOrderNumber as string

    Dim thisLine as string = origFile.ReadLine()

    lastOrdernumber = thisLine.Substring(0, thisLine.IndexOf(","))

    ' Read through the file and write the output back out to the new file

    ' inserting newlines as appropriate

    While Not origFile.EndOfStream

    dim thisNewLine as string = origFile.ReadLine()

    ' Is this the same order? If not, add a blank line between

    If lastOrderNumber <> thisNewLine.Substring(0, thisNewLine.IndexOf(",")) Then

    newFile.WriteLine()

    lastOrderNumber = thisNewLine.Substring(0, thisNewLine.IndexOf(","))

    End If

    newFile.WriteLine(thisNewLine)

    End While

    ' Flush the file buffer

    newFile.Flush()

    newFile.Close()

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Thanks for the response Tim. This is a new script approach than the one i had in mind but exactly the same concept i was thinking of as follows:

    1) Run existing SQL for Orders/Orderdetails

    2) Script Task

    - Use the Datareader for the SQL

    - Loop Through each row

    - Assign variable storing previous row ID

    - Write the row data to a new file output or a blank line if the ID is different than the variable.

    I am half way completing my version of the script which appears to be working so far but i will try and use yours and post my results.

    Thanks for the code.

  • If your SQL is really nifty, you may be able to code this up front in your SELECT statement by having a column at the end of every record which is either NULL (in the case of the row not being the final row in a batch) or char(13) + char(10) for a final row.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • We already attempted to create the line break ASCII characters as part of the SQL Statement but then it just became too difficult to build a top down query using variables.

    The trick with doing it in the SQL statement is the decision criteria passing through to the next row. Once you realise that you need to then create a looping dynamic SQL statement so you can assign the ID from the first row and pass it through to the second row and so on for checking if a blank line should be inserted.

    It was all too hard in the end with type matching, variables and While loops for dynamic top down SQL.

    Much quick and easier doing it through a script task and so much more potential for changes, data manipulations, etc...

    Thanks for the idea though.

  • 🙂 I never said that it was going to be easy.

    But there are some damn clever people in this forum and I'll bet that more than one of them could help you write that query without it being too complex to maintain. I like the idea because it solves your problem in one hit rather than two and (potentially, if a set-based solution can be found) avoids slow row-by-row updates.

    Anyhow, sounds like you are well on the way to solving it & I'm guessing that performance is not your primary concern.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil, point taken. Therefore i'll put the feelers out there to anyone who may have an SQL based solution?

    Once i get this script worked out i'll post it up to the forum with an answer in case anyone finds it useful.

    Thanks again.

  • No probs - it's an interesting thread.

    To get the maximum response, I suggest that you create a new thread in the T-SQL forum specifically with this requirement, eg

    Given the following data:

    OrderID, ProductID, ItemPrice

    1,1000,50.00

    1,1001,75.00

    2,1000,50.00

    3,1001,75.00

    3,1002,95.00

    3,1003,44.00

    How can I write a SELECT statement to return the following:

    OrderID, ProductID, ItemPrice

    1,1000,50.00, NULL

    1,1001,75.00, crlf

    2,1000,50.00, crlf

    3,1001,75.00, NULL

    3,1002,95.00, NULL

    3,1003,44.00, crlf

    where crlf = CHAR(13) + CHAR(10)?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply