October 19, 2010 at 2:06 am
Hi All,
I need your expert advice here,
I have 5000 records to transform into csv file and due to the application limitation, the upload module to upload those 5000 records only can be done by batch of 100-200 record at one time.
Because of this, i try to use for loop to split the records into a few files e.g EE_1.csv, EE_2.csv.
The records was retrieved and saved into an object variable (ResultSet) before entering the For Loop flow
The issue i encounter, when it was on the 1st loop everything was fine, it is manage to generate X Records i specified but when it go for 2nd loop, the ResultSet is returning 0 Records..when the actual total records is 5000
Anyone can help this newbie what are the mistakes i made ?
The logic is something like
Public Overrides Sub CreateNewOutputRows()
oledbAdapter = New OleDb.OleDbDataAdapter
dataTable = New DataTable
oledbAdapter.Fill(dataTable, Me.Variables.resultset)
StartPoint = Me.Variables.FilesNumber 'To know which row to start
StartPoint = (StartPoint - 1) * Me.Variables.MaxRecord
EndPoint = (StartPoint + Me.Variables.MaxRecord) - 1
If dataTable.Rows.Count - 1 < EndPoint Then
EndPoint = dataTable.Rows.Count
ContinueLoop = False
End If
For Counter = StartPoint To EndPoint
Output0Buffer.AddRow()
delimitedValues = String.Empty
dataRow = dataTable.Rows(Counter)
For i = 0 To dataRow.ItemArray.GetUpperBound(0)
delimitedValues = delimitedValues + dataRow.Item(i).ToString()
If i <> dataRow.ItemArray.GetUpperBound(0) Then
delimitedValues = delimitedValues + ","
End If
Next
Output0Buffer.DTSTR = delimitedValues
Next
oledbAdapter.Dispose()
dataRow = Nothing
dataTable.Dispose()
End Sub
October 19, 2010 at 11:08 am
I'm not really good with the scripting code, but I have an alternate suggestion. 2, actually.
Pull the data into a temp table with an identity field and then use that identity field as your row number. Caveat, this won't work if you're doing a Select INTO and include a column from the original table which is already that table's identity field. You can't have two identities on one table.
Use the Row_Number function to order the rows and pull by the Row_Number.
You can do both of these in T-SQL, though there are a lot of ways to handle it in SSIS.
October 19, 2010 at 12:23 pm
I have to agree with Brandie.
Scripting isn't really necessary to solve this problem. SSIS (or T-SQL) offers enough elegant possibilities to handle this.
You can use the IDENTITY property as said before, and with some clever use of looping you can easily divide the data into the chunks you need.
I believe there are plenty of articles on this site who describe this principle.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2010 at 11:16 am
Hi Guys,
Thanks for the reply.
I manage to get some solution from the other forum.. can't remember the forum name.
But here is the solution
Before i enter the loop container, i use the script task to assign the resultset to another object variable (let say resultset2)
In the loop container, i use resultset2 instead of resultset and it is working fine
It is not my solution but i hope it can help some people in this forum that is looking for solution.
Here is the codes
Public Sub Main()
Dim oledb As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
oledb.Fill(dt, Dts.Variables("resultset").Value)
Dts.Variables("resultset2").Value = CType(dt, DataTable)
oledb.Dispose()
dt.Dispose()
Dts.TaskResult = Dts.Results.Success
End Sub
October 26, 2010 at 11:25 am
Dread,
Glad your problem is resolved and thank you for posting your solution.
If you ever do remember the forum name or the forum thread, please make sure to link it properly.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply