April 24, 2009 at 2:15 pm
Hi Experts,
I have one old server which runs on MS SQL 200 and using some DTS package , When I am executing manually its running fine but when I am scheduling job then my job getting failed and I could see the error information as below
Executed as user: PE\SQLdtsFSDBVS_SVC. ...ng... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Dr. The step failed.
Please let me know your suggestions
Thanks
ichbinraj
April 25, 2009 at 6:13 am
This to too vague to understand what is the problem .Can you enable logging in SQL Server DTS package which ever is causing the problem.
April 25, 2009 at 6:13 am
This to too vague to understand what is the problem .Can you enable logging in SQL Server DTS package which ever is causing the problem.
April 25, 2009 at 9:25 am
Hi ,
Can you be more specific..
Job is enabled and tried using different users like service account and SA still not working
Thanks
ichbinraj
April 27, 2009 at 10:10 am
Open the failing package in DTS Designer, click on Package - Properties - Logging tab. Check the 'Log package execution to SQL Server' box and select the server where the package runs. Click OK and save the package. Run the package again and when it fails, right-click on the package in Enterprise Manager and select 'Package Logs...'. Find the errors and post the error messages here.
Greg
April 27, 2009 at 11:09 am
Hi thanks for your information
Here is the DTS Package error log.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 18
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
Thanks
ichbinraj
April 27, 2009 at 11:13 am
Does your package connect to a network file ? Maybe you don't have permission to a folder.
April 27, 2009 at 11:27 am
Hi ,
Job is running under service account and I have explicitly added this account to admin group on that network path server.
one more thing .. i tried changing user names but still no luck...
Thanks
ichbinraj
May 27, 2009 at 12:29 pm
Did you ever get a resolution to this problem?
May 28, 2009 at 3:13 am
You definitely have a problem with Windows permissions.
Try logging on to the server that runs that package using the account that runs the package. Make sure you check the SQL Agent job history to confirm which account Agent is using. Do NOT assume you know which account is being used - check what Agent says it is doing.
Then run the command you have in the SQL Agent job in a CMD window.
If you get the Permission Denied error then try to isolate where this is happening. If it is in VBScript, are you able to run any VBscript from your CMD window. If you can, then try running the VBScript that is embedded in your DTS package in a CMD window. Eventually by trial and error you should be able to find what permission you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 28, 2009 at 7:19 am
Hi Gurus,
Server is in cluster group.
First node has Oracle path set up correctly and it is running fine ... but when sql failover to 2 node DTS having problem ... I found that 2 node oracle path not setup properly in Environment variables Path ..
Thanks for following up this issue.
Thanks
ichbinraj
February 28, 2020 at 7:49 pm
I have a dts package which is running from past 6 years with out any issue . Now it is giving the below error.
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error on Line 13
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Error source: Microsoft Data Transformation Services (DTS) Package
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Help file: sqldts80.hlp
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Help context: 4500
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error Detail Records:
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error: -2147220482 (800403FE); Provider Error: 0 (0)
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Error string: Error Code: 0
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error Source= Microsoft VBScript runtime error
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error Description: File not found
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@Error on Line 13
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Error source: Microsoft Data Transformation Services (DTS) Package
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Help file: sqldts80.hlp
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@ Help context: 4500
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@DTSRun OnFinish: DTSStep_DTSActiveScriptTask_5
Feb 28, 2020 1:03:07 PM:@
Feb 28, 2020 1:03:07 PM:@DTSRun: Package execution complete.
Feb 28, 2020 1:03:07 PM:@
Error Output:
Alerting of failure result.
DEBUG: setDebug: JavaMail version 1.4ea
DEBUG: getProvider() returning javax.mail.Provider[TRANSPORT,smtp,com.sun.mail.smtp.SMTPTransport,Sun Microsystems, Inc]
DEBUG SMTP: useEhlo true, useAuth false
DEBUG SMTP: trying to connect to host "ar.email.cibc.com", port 25, isSSL false
220 CBSCC-X10-AR01.ad.cibc.com Microsoft ESMTP MAIL Service ready at Fri, 28 Feb 2020 13:03:07 -0500
DEBUG SMTP: connected to host "ar.email.cibc.com", port: 25
EHLO ibcs.cibc.com
250-CBSCC-X10-AR01.ad.cibc.com Hello [159.231.173.75]
250-SIZE 26214400
250-PIPELINING
250-DSN
250-ENHANCEDSTATUSCODES
250-STARTTLS
250-AUTH
250-8BITMIME
250-BINARYMIME
250 CHUNKING
DEBUG SMTP: Found extension "SIZE", arg "26214400"
DEBUG SMTP: Found extension "PIPELINING", arg ""
DEBUG SMTP: Found extension "DSN", arg ""
DEBUG SMTP: Found extension "ENHANCEDSTATUSCODES", arg ""
DEBUG SMTP: Found extension "STARTTLS", arg ""
DEBUG SMTP: Found extension "AUTH", arg ""
DEBUG SMTP: Found extension "8BITMIME", arg ""
DEBUG SMTP: Found extension "BINARYMIME", arg ""
DEBUG SMTP: Found extension "CHUNKING", arg ""
DEBUG SMTP: use8bit false
MAIL FROM:<scheduled-task.error@ibcs.cibc.com>
250 2.1.0 Sender OK
RCPT TO:<mailbox.ibcs-cibis@cibc.com>
250 2.1.5 Recipient OK
DEBUG SMTP: Verified Addresses
DEBUG SMTP: mailbox.ibcs-cibis@cibc.com
DATA
354 Start mail input; end with <CRLF>.<CRLF>
Date: Fri, 28 Feb 2020 13:03:07 -0500 (EST)
From: scheduled-task.error@ibcs.cibc.com
To: mailbox.ibcs-cibis@cibc.com
Message-ID: <159257116.01582912987827.JavaMail.BPSVC_AUTOSYSIBCSPRD@SCCCIBISPRDDB01>
Subject: Failure -- Job ID: BCG6 - 5I
MIME-Version: 1.0
Content-Type: multipart/mixed;
@boundary="----=_Part_0_2015322662.1582912987640"
------=_Part_0_2015322662.1582912987640
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
The result of job BCG6 - 5I was: Failure
March 1, 2020 at 4:46 pm
I am guessing a little bit, and haven't used DTS for a few years, but I think you have two issues:
Line 13 of the script can't find a file
Possibly an error routine is then trying to send an email and fails to authenticate to an SMTP email server.
Can you have a look at line 13 and see which file it's trying to access? I would think that this file is either not there or the account running the package doesn't have permission for it.
Have any package permissions changed recently?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply