Why does an error occur when converting a DTS package into a job?

Why does an error occur when converting a DTS package into a job?

Where does DTS package run?

Why cannot DTS run properly after it is packaged into a job? We run everything normally from the Enterprise Manager, but we cannot use it as a job. Because it runs in different environments, this environment refers to security context, security environment or security context. As a programmer, you can run a program in front of a workstation. DTS is in the environment of the machine in front of you, but if it is used as a job, it will always run on the server.

As a programmer, You can import data from a text file, but the specified file in DTS must also exist on the server and have sufficient permissions to support operations on this file.

Who is running the DTS package? Is a service called SQL Agent. This job has an owner. The owner can be an SQL Server logon or an NT account. You can use the Enterprise Manager to directly view who the owner is, or you can run MSDB. DBO. sp_help_job to know who the owner is.

For SQL Server 7.0, the security context is determined by the job owner. If the owner is a user without the SysAdmin role, the package runs under the sqlagentcmdexec account. Of course, the security context used is the account, therefore, this account must have sufficient permissions to run the specified operation. Generally, the sqlagentcmdexec account does not have permissions on computers other than the server, so access to files on other machines will certainly fail.

For SQL Server 2000, the security context is also determined by the job owner. If the owner is a user without the SysAdmin role, the package runs under the SQL Agent Proxy account and uses the permissions of this user. For SQL Agent Proxy, it can run operations connected to the database. Of course, it must also have the corresponding database and NT permissions. For the execution of the DTS package, the SQL Agent Proxy account must have the read and write permissions on the temporary directory for the Account to run. The directory is: C: /Documents and Settings/<account>/Local Settings/temp

If a job is owned by a member of the SysAdmin database role, the job runs under the account that starts the SQL Agent service. At the same time, if the role is owned by the NT domain user and is stored in the database, you must use the user in the same domain or trust domain to start the SQL Server service. For example, if the SQL Agent job is owned by users in the USA domain, the user who starts the SQL Server service must be a trusted domain from the USA domain or USA domain. If SQL Server is started by a local account, the execution of the package fails. Who is the owner of a DTS package? At this time, the owner should check who logged on to the enterprise manager. If the database uses NT authentication, the job owner will be the NT account for starting the SQL Agent service; if you log on to the Enterprise Manager using SQL Server Authentication (for example, using SA to log on), this SQL Server user is the main user. If you want to change the owner of a package, you can implement it in enterprise management. Right-click the package and choose "General. In the query analyzer, MSDB. DBO. sp_update_job is used.

If dtsruns through dtsrun.exe, the security context is the user logging on to the computer at this time. If you run dtsrun.exe through xp_mongoshell, if this user is a user in the SysAdmin role, it starts the SQL Server service, which is the security context. If this user is not a sysadminuser, dtsrun.exe runs in the sqlagentcmdexec account. If SQL Server is started as a local account, the DTS package does not have the permission to access resources of other machines.

If SQL Server is started under an NT account, its permissions are the same as those of the NT account. If the account is a local account, the DTS package does not have permissions on other machines. If the account is a domain user, the package can access resources of other computers in the domain. Sometimes we use an NT authentication connection in the DTS package. The security context of this connection is consistent with the security context of the package. If you run dtsrun.exe on the command line, the security certificate obtained here is the certificate of the current nt login user. If the package runs as a job, the security context of the connection will be the account for starting the SQL Agent. We assume that the package owner is a member of SysAdmin.

An error occurs when we map the drive. Because the mapped drive does not exist physically, and the SQL Agent is an NT Service, the NT Service cannot see the mapped drive. Ing is part of a user script, and the service does not call the content of the user script. Use the UNC path to call resources on other machines. It is recommended that you do not use the relative path, because the running of the DTS package will be transferred from the debugging machine to the server, so the relative path is not easy to use. For the use of COM components, make sure that the corresponding COM components are also available on the server. Although the package itself has some passwords to provide, such as the package owner's password and user password, these things have nothing to do with the running environment. If sqlagentcmdexec has insufficient permissions to run the package, the following error message is generated:

Dtsrun: loading... dtsrun: executing... dtsrun onstart: dtsstep_dtsexecutesqltask_1 dtsrun onerror: dtsstep_dtsexecutesqltask_1, error =-2147217843 (80040e4d) Error string: Login Failed for user 'nt _ name/sqlagentcmdexec '. error Source: Microsoft ole db provider for SQL Server Help File: Help context: 0 error detail records: Error:-2147217843 (80040e4d); provider error: 18456 (4818) Error string: login Failed for user 'nt _ name/sqlagentcmdexec '. error Source: Microsoft ole db provider for SQL Server Help File: Help context: 0 dtsrun onfinish: dtsstep_dtsexecutesqltask_1 dtsrun: Package execution complete. process Exit Code 1. the step failed.

You must grant sqlagentcmdexec sufficient logon and database permissions.