Multiple Data Driven Query Tasks

#1

I have 4 Data Driven Query Tasks executing within a Package. All of these perform inserts/updates/deletes on destination tables using data sourced from staging tables within the same database (so the Binding and Destination tables for all 4 Tasks have the same connections). When more than one of the Tasks has the 'Join Transaction' option set (I need this for rollback purposes) I get the following error message 'Transaction Context In Use by another session' on execution of the second task. I have read about this problem/bug on the Microsoft Web Site.
Does anyone know of any way around this problem, as I need to use Transformations in order to be able to use the error handling in DTS to see Source Error Records (it is a requirement to determine which records cause errors in the DTS). If I use Transact SQL (e.g. Insert into tblDest select * from tblStaging) then I cannot pinpoint exactly which records
cause errors

Many Thank
Gaye

#2

Are they all seperate connection objects i.e. Conn1 , Conn2, Conn3 and do
not derive from one connection?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I have 4 Data Driven Query Tasks executing within a Package. All of these
perform inserts/updates/deletes on destination tables using data sourced

from staging tables within the same database (so the Binding and Destination
tables for all 4 Tasks have the same connections). When more than one of the
Tasks has the 'Join Transaction' option set (I need this for rollback
purposes) I get the following error message 'Transaction Context In Use by
another session' on execution of the second task. I have read about this
problem/bug on the Microsoft Web Site.

Quote:

Does anyone know of any way around this problem, as I need to use
Transformations in order to be able to use the error handling in DTS to see

Source Error Records (it is a requirement to determine which records cause
errors in the DTS). If I use Transact SQL (e.g. Insert into tblDest select *
from tblStaging) then I cannot pinpoint exactly which records

Quote:

cause errors.

Many Thanks
Gaye

#3

I have 2 Connections called 'Corporate Database' and 'Corporate Database II', both of which refer to the same database. Each of the 4 tasks uses the first connnection for the source and the second connection for the bindings connection.

#4

Are they all seperate connection objects

When you defined them did you specify "Existing Connection" ?

Can you not join them together using workflow so they all execute
individually one after the other (serially).

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I have 2 Connections called 'Corporate Database' and 'Corporate Database
II', both of which refer to the same database. Each of the 4 tasks uses the

first connnection for the source and the second connection for the bindings
connection.

#5

Both the Connection objects I have are defined seperately (i.e. I did not select 'Existing Connection') .
I am using DTS workflow in order to run them serially (i.e if one fails no more are executed). However when the second task executes I get the 'Transaction Context in use by another session'.
Should I have 8 connection objects instead of 2, i.e each of the 4 tasks would have a different Source and Binding connections instead of reusing them?

#6

In the workflow properties can you set "Close Connection on completion"

If I use this task I have different connections for each connection yes.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Both the Connection objects I have are defined seperately (i.e. I did not
select 'Existing Connection') .
I am using DTS workflow in order to run them serially (i.e if one fails no
more are executed). However when the second task executes I get the

'Transaction Context in use by another session'.

Quote:

Should I have 8 connection objects instead of 2, i.e each of the 4 tasks
would have a different Source and Binding connections instead of reusing

them?

#7

I have the "Close Connection on completion" option set for each of the tasks. I also now have 8 connection objects in use by the 4 tasks. The error is still occuring - the only way to run the package successfully is to uncheck the 'Join Transaction' option, however I don't want to do this

----- Allan Mitchell wrote: ----

In the workflow properties can you set "Close Connection on completion

If I use this task I have different connections for each connection yes

--

---------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP
www.allisonmitchell.com - Expert SQL Server Consultancy
www.SQLDTS.com - The site for all your DTS needs
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:6AB9D382-E4C6-4A97-BF1C-0486D7226B4F (AT) microsoft (DOT) com..

Quote:

Both the Connection objects I have are defined seperately (i.e. I did no
select 'Existing Connection')
I am using DTS workflow in order to run them serially (i.e if one fails n
more are executed). However when the second task executes I get th

'Transaction Context in use by another session'

Quote:

Should I have 8 connection objects instead of 2, i.e each of the 4 task
would have a different Source and Binding connections instead of reusin

them

#8

Oh sorry, I didn't read that you were using transactions in the package.

Why are you using DDQs?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I have the "Close Connection on completion" option set for each of the
tasks. I also now have 8 connection objects in use by the 4 tasks. The error

is still occuring - the only way to run the package successfully is to
uncheck the 'Join Transaction' option, however I don't want to do this.

Quote:

----- Allan Mitchell wrote: -----

In the workflow properties can you set "Close Connection on
completion"

If I use this task I have different connections for each connection
yes.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Gaye Finn" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message
news:6AB9D382-E4C6-4A97-BF1C-0486D7226B4F (AT) microsoft (DOT) com...
Both the Connection objects I have are defined seperately (i.e. I
did not
select 'Existing Connection') .
I am using DTS workflow in order to run them serially (i.e if one
fails no
more are executed). However when the second task executes I get the
'Transaction Context in use by another session'.
Should I have 8 connection objects instead of 2, i.e each of the 4
tasks
would have a different Source and Binding connections instead of
reusing
them?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Multiple Data Driven Query Tasks

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Multiple Data Driven Query Tasks - 02-09-2004 , 05:31 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 05:38 AM

RE: Multiple Data Driven Query Tasks - 02-09-2004 , 06:01 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 06:37 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 07:11 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 07:18 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 08:41 AM

Re: Multiple Data Driven Query Tasks - 02-09-2004 , 08:48 AM