The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Help.. Need faster record update
Posted: 11 Nov 08 10:11 AM
|
Does anyone have any ideas as to how i can update 1000s of recodes through SSIS in SLX. Looping with a data reader is extremely slow. I am updating based on a record set in another database.
I have a script task doing the following but it is rediculously slow
Dim conn As New OleDbConnection(Dts.Connections.Item("SLX").ConnectionString)
Try
conn.Open()
Dim cmd As New OleDbCommand("select something from somewhere join on something", conn)
Dim reader As OleDbDataReader = cmd.ExecuteReader()
Dim sql As String
While reader.Read()
Try
sql = "update something"
Dim cmd2 As New OleDbCommand(sql, conn)
cmd2.ExecuteNonQuery()
Catch ex As Exception
MsgBox("An error occurred: " & ex.Message, MsgBoxStyle.Exclamation)
End Try
End While
reader.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Exclamation)
Finally
conn.Dispose()
conn = Nothing
End Try
|
|
|
|
Re: Help.. Need faster record update
Posted: 11 Nov 08 11:04 PM
|
Use an OleDBDataAdaptor, do the updates in memory and then post them to disk in batches rather than one at a time.
Phil |
|
|
|
Re: Help.. Need faster record update
Posted: 12 Nov 08 1:26 PM
|
| I tried to implement your suggestion but SSIS keeps complaining that i need to implement system.xml (which i already am). Any suggestions? |
|
|
|
Re: Help.. Need faster record update
Posted: 13 Nov 08 2:32 AM
|
Perhaps a reference is missing - merely adding an 'implements' line does not drag in references. If you've tried this, please post the exact error message and I will try to help you again when I have my work PC in front of me.
Phil |
|
|
| |
|