Inconsistent DTS Error - Help please

[Ryan]

I get an error every so often with a DTS package on SQL 7. Error as
follows.

The connection is currently being used by a task. The connection
cannot be
closed or re-used.

This doesn't happen all the time and I can sometimes (more often than
not) get the DTS package to complete in it's entirety.

To explain what the DTS package does...

Truncate tables in reporting environment(several in a batch)
Clear Transaction Logs
Copies data from live environment into CSV (for speed)
Copies data from CSV files into tables previously truncated.
Builds up a table based on the data copied (for reporting)
Clear Transaction logs

I'm using a pretty basic set up, Connection (1st DB) -> Transformation
to CSV -> Transformation to Connection (2nd DB). It seems to fail on
either the first or second transformation at random (?).

I've checked the transformations so that they close the connection
afterwards so it should in theory be releasing the CSV files for the
next step. I suspect that there is a timing issue with this. I can
copy the CSV files over, but this is a little sloppy and I would
prefer not to do it.

Any ideas how to find a tidy way to ensure these are closed both
before and afterwards ?

Thanks

Ryan

[Simon Hayes]

"Ryan" <ry********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

I get an error every so often with a DTS package on SQL 7. Error as
follows.

The connection is currently being used by a task. The connection
cannot be
closed or re-used.

This doesn't happen all the time and I can sometimes (more often than
not) get the DTS package to complete in it's entirety.

To explain what the DTS package does...

Truncate tables in reporting environment(several in a batch)
Clear Transaction Logs
Copies data from live environment into CSV (for speed)
Copies data from CSV files into tables previously truncated.
Builds up a table based on the data copied (for reporting)
Clear Transaction logs

I'm using a pretty basic set up, Connection (1st DB) -> Transformation
to CSV -> Transformation to Connection (2nd DB). It seems to fail on
either the first or second transformation at random (?).

I've checked the transformations so that they close the connection
afterwards so it should in theory be releasing the CSV files for the
next step. I suspect that there is a timing issue with this. I can
copy the CSV files over, but this is a little sloppy and I would
prefer not to do it.

Any ideas how to find a tidy way to ensure these are closed both
before and afterwards ?

Thanks

Ryan

I don't know of a specific solution to your problem, but you may be able to
use an ActiveX step to check the DTS Connection object's Connected property,
and call ReleaseConnection if necessary.

If that isn't helpful, you may want to post this in a more specialized
forum, such as microsoft.public.sqlserver.dts.

Simon

[Ryan]

That's done the trick. Thanks for the help. Used the following code :

Function Main()
Dim oPkg
Dim oConn

Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections("myConnection")

oConn.ReleaseConnection
Main = DTSTaskExecResult_Success
End Function

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...

"Ryan" <ry********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

I get an error every so often with a DTS package on SQL 7. Error as
follows.

The connection is currently being used by a task. The connection
cannot be
closed or re-used.

This doesn't happen all the time and I can sometimes (more often than
not) get the DTS package to complete in it's entirety.

To explain what the DTS package does...

Truncate tables in reporting environment(several in a batch)
Clear Transaction Logs
Copies data from live environment into CSV (for speed)
Copies data from CSV files into tables previously truncated.
Builds up a table based on the data copied (for reporting)
Clear Transaction logs

I'm using a pretty basic set up, Connection (1st DB) -> Transformation
to CSV -> Transformation to Connection (2nd DB). It seems to fail on
either the first or second transformation at random (?).

I've checked the transformations so that they close the connection
afterwards so it should in theory be releasing the CSV files for the
next step. I suspect that there is a timing issue with this. I can
copy the CSV files over, but this is a little sloppy and I would
prefer not to do it.

Any ideas how to find a tidy way to ensure these are closed both
before and afterwards ?

Thanks

Ryan

I don't know of a specific solution to your problem, but you may be able to
use an ActiveX step to check the DTS Connection object's Connected property,
and call ReleaseConnection if necessary.

If that isn't helpful, you may want to post this in a more specialized
forum, such as microsoft.public.sqlserver.dts.

Simon