Problemer med en DTS-package

[Jim Andersen]

Hejsa,

Jeg har lavet en DTS-package og den virker når jeg kører den pr. håndkraft,
men når den køres af SQL Server Agenten, fejler den.

Det skal også lige siges at jeg har brugt guiden til at oprette en
"standard" vedligeholdelse/backup rutine. Den går heller ikke så godt. Den
kører optimization's men ikke integrity check eller backup (vist fordi
integrity check'et fejlede).

Følgende er i log-filen:
Executed as user: HT-DOMAIN\INTRAB-SQL. ...:

DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError:
DTSStep_DTSDataPumpTask_1,

Error = -2147024891 (80070005) Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 1100 Error Detail Records:
Error: -2147024891 (80070005); Provider Error: 0 (0) Error string:
Access is denied. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
1100 Error: -2147024891 (80070005); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. Access is denied.
Error source: Micr... Process Exit Code 1. The step failed.

Log-fil slut.

Mine "data-sources"/Connections er sat til at bruge sql-server
authentication hvor jeg angiver superbruger navn+kodeord, ikke windows
authentication.

Hvis jeg i SQL-EM kigger lidt på bruger intrab-sql:
Under Security - Logins HT-DOMAIN\intrab-sql:
Faneblad General: Grant Access
Faneblad Server Roles: System Administrators
Faneblad Database Access: checkmark ved Mbestil, user=dbo; database roles
for mbestil = public + db_owner

Jeg har også en smule svært ved at se hvor det fejler, for selv om jeg har
givet de enkelte "steps" i DTS-pakken nogen "gode" navne, så er det stadig
de "gamle" navne der bruges i log-filen.

Det DTS-pakken gør, er at tømme en tabel, derefter (forhåbentlig, jeg synes
ikke det er særligt nemt at sætte rækkefølgen af steps op) hente data fra en
anden SQL-server over i den tomme tabel etc etc.

Er der nogen der har nogen gode ideer ?

/jim

[Jim Andersen]

Ups.. this isn't a .dk group :-)

Hi,

I made a DTS-package and it works when I execute it manually, but when it is
run by the SQL Server Agent, it fails.

I have use the guide to create a maintenance plan. That doesn't work so good
either. It runs Optimizations, but not integrity checks or backups (probably
because integrity checks failed).

The following from the log file:
Executed as user: HT-DOMAIN\INTRAB-SQL. ...:

DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError:
DTSStep_DTSDataPumpTask_1,

Error = -2147024891 (80070005) Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 1100 Error Detail Records:
Error: -2147024891 (80070005); Provider Error: 0 (0) Error string:
Access is denied. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
1100 Error: -2147024891 (80070005); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. Access is denied.
Error source: Micr... Process Exit Code 1. The step failed.

Log-file endeth here.....

My "data-sources"/Connections is set up to use sql-server
authentication where I enter superuser name+password, not windows
authentication.

If I browse around using SQL-EM:
Under Security - Logins HT-DOMAIN\intrab-sql:
Tab General: Grant Access
Tab Server Roles: System Administrators
Tab Database Access: checkmark at Mbestil, user=dbo; database roles
for mbestil = public + db_owner

I also have som problems identifying where the package fails. I have given
the individual "steps"/"transformations" some pretty good names, but in the
log-file it still shows the "old" names.

The DTS package empties a table, then fills it by copying data from another
table in another database on another server.

Anyone with some useful tips ?

/jim

[Jim Andersen]

Jim Andersen wrote:

I also have som problems identifying where the package fails. I have
given the individual "steps"/"transformations" some pretty good
names, but in the log-file it still shows the "old" names.

Have solved this by using "disconnected edit". can change a lot of stuff
there that is inaccessible via the GUI.

And then I found out that the "problem step" (as the only one) was set up to
log any errors to a logfile.txt on the Desktop......

Problem solved.

/jim