June 10, 2015 at 6:16 am
A couple of DTS in SQL 2008, which was running fine from job for more than a year has started failing. All those DTS export data to Excel 2003. All the failing DTS if run manually as DTS, runs fine. It fails only from job. All the failing jobs have the same error message given below. Any idea how to fix this?
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
June 11, 2015 at 9:27 am
Any chance it's now returning more than 65536 rows? Also, check the destination spreadsheet and see if it's getting any partial data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2015 at 9:36 am
Does the entity that is running the job have rights to where the Excel file is being created?
June 11, 2015 at 10:51 am
djj (6/11/2015)
Does the entity that is running the job have rights to where the Excel file is being created?
Yep, this is another possibility, especially if someone recently changed the permissions on that folder, or perhaps on a higher-level folder.
EDIT: It could also be Share permissions having changed, if the folder is not on the SQL Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 22, 2015 at 1:55 am
Before the DTS/JOb runs, I am copying an empty excel template and then running the DTS. Data never gets more than 100 rows.
SQL Agent id has full access to folder/excel/share folder.
June 22, 2015 at 2:29 am
To further add, the DTS when run from Windows batch/command file runs fine. ONly when the dts is run from Job (using DTSRun), it fails with the error.
June 23, 2015 at 10:21 am
Any ideas why i can run DTS as a scheduled task (from Windows command) but not from job.
Job was running for long time without any problem. Now only, i starteed to fail.
June 23, 2015 at 10:36 am
Did ANY permissions change? (SQL, Windows)
Were any updates installed? (Windows)
Did the Excel version on the server change?
June 23, 2015 at 2:47 pm
balasach82 (6/22/2015)
To further add, the DTS when run from Windows batch/command file runs fine. ONly when the dts is run from Job (using DTSRun), it fails with the error.
Exactly what error message do you get? You say "DTSRun"... are you referring to the SQL Agent service, or to something else?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 6:08 am
ERROR is same for all these failures.
IN CMD, I use DTSRUN /S SERVERNAME / E /N DTSNAME
The same query is also used in job. CMD is executing from my ID wich is server admin too. I tried executing the job from my id. It also fails.
**********************************
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
**********************************
June 24, 2015 at 8:22 am
balasach82 (6/24/2015)
ERROR is same for all these failures.IN CMD, I use DTSRUN /S SERVERNAME / E /N DTSNAME
The same query is also used in job. CMD is executing from my ID wich is server admin too. I tried executing the job from my id. It also fails.
**********************************
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
**********************************
Turns out DTSRUN is the old SQL2000 utility to run DTS packages. Something has clearly changed, in that DTSRUN may no longer have the authority it used to have, and it could be a number of different places that the effective authority has disappeared. It could be anything from permissions on a source or destination file share to data access rights. Be aware that you posted this problem in a SQL 2008 forum, so are you running SQL2008 or SQL 2000? If the former, you might want to look into using the current utility DTEXEC and converting the package to SSIS. Given that you're getting a JET database engine error and error code 80004005, this suggests an access rights issue of some kind. Whether it's the source data or the destination is an unknown, but I'd check both to be sure.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 9:12 am
Source is SQL 2005 DB
Destination: Excel 2003
Excel not installd in server. I am thinking about installing excel in server, which might resolve driver related issues.
I will also re-check and reconfirm the access.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply