July 3, 2006 at 5:05 pm
I have a dts package that is scheduled as a job. This job is created under the admin ownership.
The job is failing even when I log into the server as an admin and start(execute) the job. If I execute the dts package itself it runs successfully.
Any ideas why this may be?
Thanks,
Ninel
July 3, 2006 at 5:38 pm
Please refrain from cross-posting. A single post is sufficient.
July 5, 2006 at 11:42 am
it is almost certainly an issue with your SQL Agent service account not having rights. Make sure that any files/folders that you need to access have permission granted to the service account. Furthermore make sure the service account has rights to any databases you use as data sources.
July 5, 2006 at 11:47 am
I dug a little deeper and found the following error on the job history:
Executed as user: ITIC\itiadmin. DTSRun: Cannot create COM Server to load and execute DTS Package. Error -2147024770 (8007007E): The specified module could not be found. Process Exit Code -2147024770. The step failed.
Do you know what htis means?
Thanks,
Ninel
July 5, 2006 at 12:07 pm
Ninel,
it means that COM Server which is a a friendly name for DLL to describe it simply, can not load because DTSrun utility can not find it. For me it would mean that something is not installed or is not running or could not be found based on the PATH environmental variable.
When you execute DTS package it is you who execute the package and some remote resources may be accessible to the package. When the job runs, your message says that it is ITIC\itiadmin who is executing the job. Check if the same resources are available to this credential.
I would say, try to run DTSrun as yourself in the command prompt. Copy the line from the job properties / step properties and see if it will execute as you from the command prompt. Sometimes I found out that if I add /E to this command line for DTSrun the job would execute better. /E means Integrated Authentication.
Regards,Yelena Varsha
July 5, 2006 at 12:14 pm
This is what I have in the step of the job:
DTSRun /~Z0x5E56B39F4C6708C1ED6526B79578EB7B15BFFE13F26DAD8967476E759E98E3C96741230F26D576E4DFC4C145DA3A3410241E31C1211AC65B9AFCF15A32B85B7F5295AA7589086A3C20CE37C279D6CE1FF20242170E978ECF820E68546B68A1D0EA7191D42C8AA2B4D907BBCC8BCEE0615E6789D35509D68A339062E83FBA9CBBD555A1258F0D55DE595FCD4ACDA5D356AD6A54
Does that look right?
July 5, 2006 at 1:18 pm
Ninel,
It does look right. Copy the whole thing and paste to Command Prompt Window on the Server computer. Click Enter. See error messages.
Then run it again but add a space and /E after this long line of numbers. See my example without /E my package fails and with /E it runs:
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
H:\>DTSRun /~Z0xFCEC66D72E8F2E9575EF72A8E28C85BE739CAA492DD0DE627038A1ABE203000D
84093EF5736DD5A5A87A9671F21E3300349180C229AC53E3FD675CDADE1308287BA6B394E0F05BDE
98CE2896B02E88915AA49CAD54E6E49542398CF55FE436B8BAB51BE3E2A53AD48A09322F10350B63
D352F8D07FD562199C97E89F5F66F04EC954EFFACDDD955B11D7AC /E
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1 Rows have been transformed or c
opied.; PercentComplete = 0; ProgressCount = 1
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
H:\>DTSRun /~Z0xFCEC66D72E8F2E9575EF72A8E28C85BE739CAA492DD0DE627038A1ABE203000D
84093EF5736DD5A5A87A9671F21E3300349180C229AC53E3FD675CDADE1308287BA6B394E0F05BDE
98CE2896B02E88915AA49CAD54E6E49542398CF55FE436B8BAB51BE3E2A53AD48A09322F10350B63
D352F8D07FD562199C97E89F5F66F04EC954EFFACDDD955B11D7AC
DTSRun: Loading...
Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)
Error string: Login failed for user 'DTSuser'.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
H:\>
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply