DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
Thanks
Yes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:

>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>

DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
ThanksYes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:
>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>

DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
ThanksYes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:

>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>

DTS - SQL Server Agent Mail Issue

Hi,

Wondering if anyone there can tell me what I'm doing wrong (and please be gentle as I'm not at all techy).

I have a DTS scheduled on the Server Agent to run, and at the end it is to send a mail to notify me and several others that it has completed. Trouble is, it runs but doesn't send the mail.

However if I execute the DTS manually it does send the mail.

I have a idea it might be to do with the Profile in SQL Mail in Server Settings on the Properties of the server, but have no idea how to correct this.
Can anybody help?Check out MS KBs:

311231
315886
263556

Regards,

hmscott|||Cheers, will have a look now.

DTS

Hi,

What is good way of executing .dtsx jobs in sql 2005, when stored in file format, or msdb database. Can my sql agent read jobs from file format as well as msdb database ?

Thanks &Regards

Nitu

Yes SQLAgent can execute file system or msdb packages. I'm surprised you could not find that information in Books Online. The online version is here ...

http://msdn2.microsoft.com/en-us/library/ms141701(SQL.90).aspx

At the bottom of every Books Online page there is an option to vote for the usefulness of the content. You can do that to help us improve the content - I'm concerned that you are not finding this information there, so any help you can give us would be appreciated.

Donald Farmer

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.