use transaction with oledb connection ?

Question

use transaction with oledb connection ?

tunchinho

SSC Journeyman

Points: 79
More actions
May 20, 2009 at 1:56 pm

#209689

hello
i have a package that contains 3 execute sql tasks for transaction operation, folowed by a data flow task and a script task.
my purpose is to get the values from an xml file to sql server and then show them via the script task...in fact i am only trying to use transaction and connection manager in the script task at the same time
here is the code of script task
Public Sub Main()
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
cm = Dts.Connections("OleDbConnection Manager")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Dts.TaskResult = Dts.Results.Success
Dim cmd As OleDbCommand = New OleDbCommand("select * from Dest", conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
While dr.Read
MsgBox(dr(0).ToString)
End While
End Sub
the problem is;
-if i disable the data flow task and run the package then it works(here i assume that there are some values at my table of course)...however when i enable it and run, it just stops at the script task and throws a timeout exception.
-if i disable the execute sql tasks which runs the transaction, then it just works.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

tunchinho (5/20/2009)
hello
i have a package that contains 3 execute sql tasks for transaction operation, folowed by a data flow task and a script task.
my purpose is to get the values from an xml file to sql server and then show them via the script task...in fact i am only trying to use transaction and connection manager in the script task at the same time
here is the code of script task
Public Sub Main()
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
cm = Dts.Connections("OleDbConnection Manager")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Dts.TaskResult = Dts.Results.Success
Dim cmd As OleDbCommand = New OleDbCommand("select * from Dest", conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
While dr.Read
MsgBox(dr(0).ToString)
End While
End Sub
the problem is;
-if i disable the data flow task and run the package then it works(here i assume that there are some values at my table of course)...however when i enable it and run, it just stops at the script task and throws a timeout exception.
-if i disable the execute sql tasks which runs the transaction, then it just works.

I don't see in your code anything that explicitly closes the reader, the command and the connection. You have to close them, otherwise the transaction in your other task will wait until it timeouts (as it does) for the common resources to be released.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

tunchinho SSC Journeyman Points: 79 More actions · Answer 2

CozyRoc (5/21/2009)
tunchinho (5/20/2009)
hello
i have a package that contains 3 execute sql tasks for transaction operation, folowed by a data flow task and a script task.
my purpose is to get the values from an xml file to sql server and then show them via the script task...in fact i am only trying to use transaction and connection manager in the script task at the same time
here is the code of script task
Public Sub Main()
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
cm = Dts.Connections("OleDbConnection Manager")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Dts.TaskResult = Dts.Results.Success
Dim cmd As OleDbCommand = New OleDbCommand("select * from Dest", conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
While dr.Read
MsgBox(dr(0).ToString)
End While
End Sub
the problem is;
-if i disable the data flow task and run the package then it works(here i assume that there are some values at my table of course)...however when i enable it and run, it just stops at the script task and throws a timeout exception.
-if i disable the execute sql tasks which runs the transaction, then it just works.
I don't see in your code anything that explicitly closes the reader, the command and the connection. You have to close them, otherwise the transaction in your other task will wait until it timeouts (as it does) for the common resources to be released.

thank you for your reply

i agree with your words but since the "RetainSameConnection" setting of the oledb connection manager is true, then it wont reopen the connection before it will commit or rollback the transaction...actually if i use something like "OleDbConnection conn = new ...." in the script task instead of using the connection manager's settings then your words will be true for the situation...

moreover today i figured our that, its recommended to use ado.net connection manager...for some reason, this "retainsameconnection" property of oledb connection manager gives an error

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

I don't think you will be able to use OLE DB connection from script. The method you are calling GetConnectionForSchema is most probably setting up a new OleDbConnection object. The internal object that you should use is very low-level COM object, which is useless from .NET .

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/