InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:37:47 GMT20http://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxWell 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 SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.IOImports System.Data.SqlClientPublic 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 SubEnd ClassSat, 07 Mar 2009 21:43:42 GMTbkirkhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxNo 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[i]Given the following data:OrderID, ProductID, ItemPrice1,1000,50.001,1001,75.002,1000,50.003,1001,75.003,1002,95.003,1003,44.00How can I write a SELECT statement to return the following:OrderID, ProductID, ItemPrice1,1000,50.00, NULL1,1001,75.00, crlf2,1000,50.00, crlf3,1001,75.00, NULL3,1002,95.00, NULL3,1003,44.00, crlfwhere crlf = CHAR(13) + CHAR(10)?[b][/b][/i]Fri, 06 Mar 2009 04:06:46 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxPhil, 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.Thu, 05 Mar 2009 21:57:19 GMTbkirkhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspx:) 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.PhilThu, 05 Mar 2009 20:42:22 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxWe 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.Thu, 05 Mar 2009 20:12:32 GMTbkirkhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxIf 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 [b]NULL[i][/i][/b] (in the case of the row not being the final row in a batch) or [b]char(13) + char(10)[i][/i][/b] for a final row. PhilThu, 05 Mar 2009 19:43:07 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxThanks 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/Orderdetails2) 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.Thu, 05 Mar 2009 16:37:10 GMTbkirkhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxYou 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.[code]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 numberDim lastOrderNumber as stringDim 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 appropriateWhile Not origFile.EndOfStreamdim thisNewLine as string = origFile.ReadLine()' Is this the same order? If not, add a blank line betweenIf lastOrderNumber <> thisNewLine.Substring(0, thisNewLine.IndexOf(",")) Then newFile.WriteLine() lastOrderNumber = thisNewLine.Substring(0, thisNewLine.IndexOf(",")) End IfnewFile.WriteLine(thisNewLine)End While' Flush the file buffernewFile.Flush()newFile.Close()[/code]hth,TimThu, 05 Mar 2009 12:10:49 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic668987-148-1.aspxWe 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)[b][u]BAD data (NO line break between Orders):[/u][/b]OrderID, ProductID, ItemPrice1,1000,50.001,1001,75.002,1000,50.003,1001,75.003,1002,95.003,1003,44.00Here 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??????[b]GOOD data:[u][/u][/b]OrderID, ProductID, ItemPrice1,1000,50.001,1001,75.002,1000,50.003,1001,75.003,1002,95.003,1003,44.00Thu, 05 Mar 2009 01:39:40 GMTbkirk