Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Export to Flat File with a blank line b/w each Order Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 1:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, Visits: 346
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
Post #668987
Posted Thursday, March 5, 2009 12:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:32 PM
Points: 1,045, Visits: 2,722
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #669515
Posted Thursday, March 5, 2009 4:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, Visits: 346
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.
Post #669825
Posted Thursday, March 5, 2009 7:43 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 4,985, Visits: 11,681
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #669886
Posted Thursday, March 5, 2009 8:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, Visits: 346
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.
Post #669897
Posted Thursday, March 5, 2009 8:42 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 4,985, Visits: 11,681
:) 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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #669911
Posted Thursday, March 5, 2009 9:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, Visits: 346
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.
Post #669929
Posted Friday, March 6, 2009 4:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 4,985, Visits: 11,681
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)?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #670063
Posted Saturday, March 7, 2009 9:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, Visits: 346
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
Post #671057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse