March 5, 2009 at 1:39 am
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
March 5, 2009 at 12:10 pm
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
March 5, 2009 at 4:37 pm
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.
March 5, 2009 at 7:43 pm
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 5, 2009 at 8:12 pm
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.
March 5, 2009 at 8:42 pm
🙂 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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 5, 2009 at 9:57 pm
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.
March 6, 2009 at 4:06 am
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)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 7, 2009 at 9:43 pm
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