DTS Job Fails When Scheduled From SQL Agent
Mar 31, 2004
Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..
Executed as user: mydomainmylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 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 ... Process Exit Code 1. The step failed.
How come it loses the path to the file when I dont run it directly?
Cheers
Mick
View 4 Replies
ADVERTISEMENT
May 13, 2008
I have one SSIS package that fails on occasion. And I can then run this in the job by itself after it fails and it runs fine. Any ideas? Any ideas on what is causing this. It is not every day but about once a week lately but it just happened again today to this is 2nd time in 4 days this has happened. It is always on this same part in the SSIS that it fails when it does fail. 4 times in last 2 weeks.
This step it is on here has a OLE DB - DB2 source and SQL server as destination. It does a couple data conversion, derived columns and then just copies the data from the db2 table to the sql server table.
Message
Executed as user: PERFORMANCEstacyadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:06:12 AM Error: 2008-05-13 06:09:22.84 Code: 0xC0202071 Source: Copy SalesTender Retail TmpSalesTenderRetail [97] Description: Unable to prepare the SSIS bulk insert for data insertion. End Error Error: 2008-05-13 06:09:23.42 Code: 0xC004701A Source: Copy SalesTender Retail DTS.Pipeline Description: component "TmpSalesTenderRetail" (97) failed the pre-execute phase and returned error code 0xC0202071. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:06:12 AM Finished: 6:09:23 AM Elapsed: 191.157 seconds. The package execution failed. The step failed.
Stacy
View 2 Replies
View Related
Nov 21, 2006
Hi,
I have created a SSIS package and tested it in V.Stdio where it runs fine and uploads file on the remote server.
Now I have scheduled it to run as a job in SQL SERVER AGENT as JOB, but it fails when it comes to the upload task?
I am quite sure as it is something with FTP connection string or security, but can't figure it out.
I have simply added the IP/login/password in the connection manager for upload task.
Any help/ guidance would be great.
Thanks,
Ad
View 4 Replies
View Related
Feb 12, 2008
Hi All,
I'm a bit lost on this...
I have an SSIS package which writes to a configured public MSMQ. When run directly through Visual Studio, file system or msdb it works fine (I guess these use me as the user).
However, when I schedule it through an SQL Agent Job it fails with the following:
Failed to write message ... to the queue.
This seems like a permissions thing to me, but I've tried giving full control to everyone in the MSMQ permissions, also explicitly giving full control to the user that the agent job executes as. Neither made a difference.
Any help would be greatly appreciated.
Cheers,
Tony Vaughan.
View 2 Replies
View Related
Jun 11, 2007
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
dtexec /f "C:Program FilesMicrosoft SQL Server90DTSPackagesIntegration Services Project estcom.dtsx"
pause
Why it's not running with SQL Server Agent???
View 7 Replies
View Related
Feb 17, 2007
Hi,
I'm tried to run the job that created from maintenance plan. It fail and give me the following message:
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAINMYNAME', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
We are using the Windows Autheniciaton mode only and my id is set as sysadmin, setadmin, processadmin in the server roles.
I don't why I can't run the job, I check the SQL agent services is login using LOCALSYSTEM.
I try to the NT admin account to login the box, but still getting the same error message.
Any idea?
Thanks!
View 5 Replies
View Related
Feb 26, 2007
Ray writes "Hi,
I tried to ask the NT admin to login the SQL server and setup a scheduled job to "Shrink the db" for testing purpose.
I tried my ID even I'm not the NT admin, but setup as SQL server as sysadmin, setupadmin, processadmin.
Then the job start and got the error.
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAINMYNAME', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
I have no idea why that happen to NT admin. then I check the Services for SQL Agent under the control panel- admin tools- services.
It shows using the LOCALSYSTEM as login for the SQL Agent service.
Any one have any idea. what security requirement to run a job other than use 'sa' account, since we are using on Windows authenciation mode only.
I must be something is missing? hope some experts here to answer my question.
Thanks!"
View 1 Replies
View Related
May 5, 2006
Hi,
Can we allow SSIS to be scheduled via SQL Agent. Is there any security concerns.
Thanks & Regards
Shri.DBA
View 4 Replies
View Related
Sep 22, 2006
I have a DTS local package that transfer data from the host SQL server to another. If I run the package directly (i.e. Right click - Execute Package) everything works as it should.
However, if I try and execute this as a Server Agent job it fails, the job history step details shows the following error:
Executed as user: MYSERVERNAMESYSTEM. ...: Drop table customer Step DTSRun OnError: Drop table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Drop table customer Step DTSRun OnStart: Create Table customer Step DTSRun OnError: Create Table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server... Process Exit Code 2. The step failed.
Whilst this is obviously a connection issue, I dont understand how this can work if executed directly and not if executed as an agent job.
Can anybody shed any light on this?
If it helps this is the sequence of events my DTS package uses
1) Connect to remote DB
2) Drop table customers
3) Create new remote customers table
4) Connect to to local DB (hosting the DTS package)
5) Select * from local customers table
6) Dump data from local select into remote DB
View 2 Replies
View Related
Mar 26, 2004
I have a DTS job that moves a text file from a workstation to the SQL server, transforms the data, pumps into a table and then archives the file. It runs fine within EM on the SQL Server, but once scheduled fails immediately on Step 1. I have logged the error as follows:
************************************************** **********
The execution of the following DTS Package succeeded:
Package Name: Load LaserGauge Data Daily
Package Description: (null)
Package ID: {6E4E8DDC-3864-43E9-B82D-300FCBCCAE63}
Package Version: {4DFF3F73-1653-4896-8DA3-A4246D41EE51}
Package Execution Lineage: {E17CD8A1-9754-413B-B129-093711326C27}
Executed On: SEQUEL
Executed By: SQLSERVICE
Execution Started: 3/26/2004 1:12:17 PM
Execution Completed: 3/26/2004 1:12:17 PM
Total Execution Time: 0.031 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' was not executed
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700
Step Execution Started: 3/26/2004 1:12:17 PM
Step Execution Completed: 3/26/2004 1:12:17 PM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0
Step 'DTSStep_DTSActiveScriptTask_3' was not executed
************************************************** **********
I have taken the following actions to resolve:
1. Tested DTS job on local SQL Server - executes without error
2. Checked security: The DTS job is owned by me, and I am a member sysadmins, etc... The SQL Job I have tried several differing accounts (Mine, Domain Administrator, and most recently a proxy account I setup called SQLDTSuser) All have failed with the same error.
3. I have checked the SQL Agent account to ensure that it has necessary rights, stopped and started SQL Agent service, same results.
4. As mentioned I setup a Proxy Account which is in Domain Admins, and Sysadmins on SQL and changed SQL Agent Properties to use this account. result was same error.
5. Set Workflow Properties on the DTS job ActiveXtask to "Execute on Main PAckage Thread" - result was same error.
I'm at a loss I have to be overlooking something obvious, but need someone to hopefully point me in the right direction. Thank you in advance for any help I can get!
The platform is SQL 2000 and I've included the first activex Task code below:
************************************************** **********
' Visual Basic ActiveX Script
'************************************************* **********
Function Main()
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim filePath
filePath = DTSGlobalVariables("gv_SourceDir").Value & "" & SourceFileName
If fso.FileExists(filePath) Then
fso.MoveFile filePath, DTSGlobalVariables("gv_WorkDir").Value & "" & DTSGlobalVariables("gv_WorkFileName").Value
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Function SourceFileName
Dim fileDate
fileDate = DateAdd("d", -1, Date)
SourceFileName = "LASERDATA" & PadLeft(DatePart("d", fileDate), 2) & PadLeft(DatePart("m", fileDate), 2) & Right(DatePart("yyyy", fileDate), 2) & ".CSV"
End Function
Function PadLeft(Value, Length)
Dim retval
retval = Value
Do While Len(retval) < Length
retval = "0" & retval
Loop
PadLeft = retval
End Function
View 5 Replies
View Related
Mar 6, 2007
I've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.
It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.
DTS DETAILS
The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.
The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:
JOB DETAILS
Name: TransferData
Enabled, Target = Local Server
Category: Uncategorized Local
Description: Execute package: TransferData
STEPS
ID: 1
Step Name: TransferData
Type: Operating System Command
On Success: Quit with Success
On Failure: Quit with Failure
PROPERTIES OF STEP
Step Name: TransferData
Type: Operating System Command (CmdExec)
Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137
Nothing special in the advanced tab
SCHEDULES TAB
ID: 20
Name: TransferData
Enabled: No
Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM
and is setup as Recurring
Nothing in Notifications Tab
I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.
I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.
Thanks in advance for any assistance with this.
View 8 Replies
View Related
Sep 28, 1999
I scheduled a Bulk copy operation, and have been getting some inconsistent failures.
Last night the job was set to run every 3 hours, I expect it to take about 1 hour to finish.
The second itteration of the job failed, the first and third ran fine.
Here is the error from DTS
Any suggestions? Is there a better/stable Data Provider for SQL?
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSBulkInsertTask_1 DTSRun OnError: DTSStep_DTSBulkInsertTask_1, Error = -2147217900 (80040E14) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040... Process Exit Code 1. The step failed.
View 2 Replies
View Related
Mar 6, 2003
I have a SQL 2000 DTS package which executes without errors when run from the DTS design screen or by right clicking the package and selecting execute.
When I schedule the package and get it to run at a certain time or highlight the job and select start, the package comes back with an error saying there was a problem with a transformation.
I have tried scheduling the DTS by right clicking on it and selecting schedule and creating a new job and using DTSRun with an operating system command. I get the same results! Help!
View 3 Replies
View Related
Jun 22, 2004
Hi,
I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.
I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.
Any ideas anyone?
Maria
View 1 Replies
View Related
Jul 20, 2005
Hi,A DTS package which is running fine when execute ,always fails asscheduled job.I read from the previous posts about MS KB article 269074, securityand MDAC, but I don't understand and don't know how to solve it.Please help.My scenerio:SERVER : MS Windows 2000 server is in a PRODUCT DOMAINMS SQL SERVER 7.0 SP2- Agent started up by SYSTEM ACCOUNT andConnection as NT AuthenticationWORKSTATION (Enterprise Manager as Tool to connect MS SQL SERVER andwrite and schedule DTS Package): MS Windows XP connect as NT Authenticationbut in WORKGROUP DOMAINBest RegardsNipon Wongtrakul
View 1 Replies
View Related
Nov 20, 2006
I have a large number of SSIS packages, which I have developed over the last few months.
Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.
I am having real troubles with packages that move tables from one database to another.
I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.
One package which will not run at all, basically just moves 15 tables from database A on my server to database B.
The package is essentially a few SQL tasks to create tables, then a data flow.
The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.
In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:
Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error
I've googled for most of the error messages there, and tried applying some of the things I found.
I've set the commit size on the OLE DB destination to 10k rows.
I've set the number of engine threads to 2.
The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.
I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers
View 6 Replies
View Related
Jun 10, 2005
I've created an SSIS package that retrieves a file from an FTP
View 13 Replies
View Related
Apr 30, 2007
Hi,
I have couple of questions about the scheduler:
I'd like my packages to run only on weekdays and disable a package if one of the first two packages fail. is it possible to do it?
Thanks
Gulden
View 1 Replies
View Related
Sep 25, 2007
Hi
I have created a package that extracts data from an Oracle 10g database into a database on SQL Server 2005 SP2.
This package run fine when run inside BIDS and also if run from DTExecUI or DTExec
(This is done while logged in using the same domain account that runs both the SQL Service and the SQL Agent Service. This account is also a local admin on the box.)
When I try to schedule this with SQL Server Agent (running under the SQL Agent Service Account) it returns an MSDAORA error
"Oracle client and network components were not found"
The same error occurs if I try to use CmdExe step to try and execute the DTExec command.
The interesting part however is that I use a lower privaleged proxy account then
(a) Executing the package directly still fails but
(b) Running a DTExec command will succed
Can anyone provide an explanation for this behaviour. While I can work around this "feature" by using the DTExec and a proxy account it does make the packages harder to configure/debug and defeats many of the configuration advantages provided by SSIS
TIA
Nadreck
View 1 Replies
View Related
Jan 3, 2007
Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.
My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.
I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!
View 4 Replies
View Related
Mar 21, 2006
My client has a number of jobs that are run overnight. We've set themup to email me when they're completed. Every morning I get in to abunch of emails like this:<quote>JOB RUN:'Tech Pubs Email Notification' was run on 18/03/2006 at00:00:00DURATION:0 hours, 0 minutes, 0 secondsSTATUS: SucceededMESSAGES:The job succeeded. The Job was invoked by Schedule 10 (SendMail). The last step to run was step 1 (Send Mail).</quote>However, the most important job - the database backup - fails everytime.<quote>JOB RUN:'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''was run on 20/03/2006 at 18:00:00DURATION:0 hours, 0 minutes, 2 secondsSTATUS: FailedMESSAGES:The job failed. The Job was invoked by Schedule 7 (Schedule1). The last step to run was step 1 (Step 1).</quote>What's strange is that the job runs successfully if you kick it offmanually (in EM: right-click and "Start Job")!!! Does anyone have anyidea of why that might be? Where to look for diagnostic information?TIAEdward
View 6 Replies
View Related
Jul 20, 2005
I am running a SQL Server 2000 installation with several databases. Eachdatabase and log is backed-up using a maintenance plan.The scheduled maintance plan for the latest database does not run, butdisplays no error. There is no entry in the job history. The same thinghappens when I try to run the individual jobs from Enterprise Manager.I've checked the database recovery model (full), the location of the backupfiles (same as the other databases), and just about everything else I canthink of. The scheduled maintenance plan for every other database runs asit should.What am I missing?ThanksIain
View 6 Replies
View Related
Jul 20, 2005
I have an ActiveX Script task in a DTS package which executes finewithin DTS.However, if I run it as a sheduled job, it fails with a 'path notfound error'. I've cut the code down to the approriate problem andposted it here, it just makes a simple text file with a name derivedfrom the current date and time and puts 'Hello World' in the filealthough in reality I go on to make an xml file. (make sure your pathexists before you execute it). Why does the scheduled job not executethis simple task. I have loads of other jobs that run packages to aschedule and the account is a domain account with windowsadministrative rights.'******************************' Visual Basic ActiveX Script'**********************Const strPath="d:xml"Private Function GetDateTime()Dim strNow, stryear, strMonth, strDay, strTimestrNow = Nowstryear = Mid(strnow, 7, 4)strMonth = Mid(strnow, 4, 2)strDay = Left(strnow, 2)strTime = Replace(Mid(strnow, 12, 8), ":", "")GetDateTime = stryear + strMonth + strDay + strTimeEnd FunctionFunction Main()Dim fso,f,MyFile 'file objectsSet fso = CreateObject("Scripting.FileSystemObject")dim strRightNowstrRightNow=GetDateTime()Set MyFile= fso.CreateTextFile(strPath &"" & "UPD-"& strRightNow & ".txt", True) 'This line fails from a scheduled jobMyFile.WriteLine("Hello World")MyFile.CloseMain = DTSTaskExecResult_SuccessEnd Function'Error Source= Microsoft VBScript runtime error Error Description:Path not found Error on Line 25
View 1 Replies
View Related
Jun 26, 2006
I've mined countless threads all over the Net where people are having my problem, but none of the solutions I've seen seem to apply.
When I run a DTS package from Enterprise Manager, everything works great. When I schedule the same package as a job, it fails immediately and reports this error:
Executed as user: SERVER-NAMESYSTEM. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
My hunch is the SERVER-NAMESYSTEM.DTSRun account doesn't have sufficient privileges, but I can't find how/where to fix that.
Help, please.
Thanks,
Doug
View 2 Replies
View Related
Apr 17, 2007
I have a SSIS package that consists of 3 Script Tasks, two Data Flow Tasks. Each script tasks executes a FTP command. The first one renames the file, the second gets the renamed file and third resets it back to original name for testing.
When I run via BIDS or DTSExec all work fine. When I schedule it, only the first script runs. It never downloads the file.
Setup - SQL 2005 SP2, SSIS is running, 32 bit OS, SQL Agent Account is local admin. Package protection level is DontSaveSensitive. PrecompileScriptIntoBinaryCode = True
What am I missing??
Thanks
Mike Allen
View 7 Replies
View Related
Apr 25, 2007
I've managed to get the basics coded into my package. Nothing fancy, it is all quite literally embedded inside the package. No config files, parameters, variables, etc. I've imported the package into SSIS. I can right click on the package and select Run Package. The package fires off, validates, runs through the entire process, and completes successfully. However, when I set up the package to run as a scheduled task, it fails almost immediately with the following output.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:28:07 AM
Error: 2007-04-25 03:28:07.41
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSroperty" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2007-04-25 03:28:11.36
Code: 0xC001602A
Source: Package Connection manager "FTP Connection Manager"
Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed
.
End Error
Error: 2007-04-25 03:28:11.36
Code: 0xC002918F
Source: Card10 FTP Task
Description: Unable to connect to FTP server using "FTP Connection Manager".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:28:07 AM
Finished: 3:28:11 AM
Elapsed: 4.297 seconds
Help.......
View 3 Replies
View Related
May 9, 2006
HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!
1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.
2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domainuser. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).
3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.
My questions:
1. How can I get more detailed logging from SQL Server Agent?
2 Any ideas about why it's failing in the first place.
Many thanks in advance.
Ben
View 17 Replies
View Related
May 31, 2001
A series of export/import jobs are scheduled on a dozen databases sitting on one of our servers, and are run at regular intervals through the day. Some of the jobs are failing with the following error recorded in the 'View Job History..':
EXCEPTION: Insufficient memory for this operation. Process Exit Code 2. The step failed.
Will this be cured by increasing the memory available to SQL Server (it has 512Mb already, 1/2 of the total physical RAM)? Also, why are only some jobs failing and others completing? Should I run performance monitor when the next schedule is?
Thanks
Derek
View 1 Replies
View Related
Apr 17, 2002
I am using SQL server 7, all service packs. We recently changed the NT Administrator password because of staffing changes. After doing so, I have 2 scheduled DTS job that are failing with an "unspecified error" -2147008507 (80074005). All other scheduled DTS packages succeed (about 20 other jobs including 1 DTS).
I have not found this error documented. I am assuming this has to do with the password change, but I have not been able to resolve it. The DTS packages run fine by hand (manually). I recreated the scheduled jobs several different ways but they still fail.
Any information on this is most appreciated! Thank-you!!
The full message is:
... DTSRun: Executing... DTSRun OnStart: Drop table [Intranet].[dbo].[tbl_phone_depts] Step DTSRun OnFinish: Drop table [Intranet].[dbo].[tbl_phone_depts] Step DTSRun OnStart: Create Table [Intranet].[dbo].[tbl_phone_depts] Step DTSRun OnFinish: Create Table [Intranet].[dbo].[tbl_phone_depts] Step DTSRun OnStart: Copy Data from Departments to [Intranet].[dbo].[tbl_phone_depts] Step DTSRun OnError: Copy Data from Departments to [Intranet].[dbo].[tbl_phone_depts] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error: -2147467259 (80004005... Process Exit Code 1. The step failed.
View 5 Replies
View Related
Feb 9, 2005
I searched the forum's threads on this, and while there were many results, none have helped so far.
I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.
Function Main()
Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")
FSO.CopyFile "\<server><Share>Data.mdb", "\<server>C$DataCopyData.mdb"
Main = DTSTaskExecResult_Success
End Function
The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(
I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.
My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.
The Scheduled Task fails with the common error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)
I am at a loss here. Thanks for your help.
View 4 Replies
View Related
Jun 2, 2006
I'm new to DTS packages, but managed to create one that successfully empties a table in SQL Server then imports data from a Foxpro file on another server into it. It runs fine if I execute it from DTS, but fails if I schedule it to run in SQL Server Agent (using the "Schedule Package" option in DTS). I think the relevant portion of the error returned when the job fails is:
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'hrpersnl.dbf' does not exist.
The file does exist. I also tried to execute it from a stored procedure, but got a similar error. Any thoughts on why it runs one way but not the other?
TIA
View 14 Replies
View Related
May 7, 2006
I have a SSIS package with an FTP task to download a Excel file and populate a table using an excel connection mnager and a SQL Server Destination and it always fails with the following error when scheduled:
The job failed. The Job was invoked by User sa. The last step to run was step 1 (FTP-DM-CRN_ALLOCATION_COMMENTS).
Executed as user: WEB-INTSQLSYSTEM. The package execution failed. The step failed.
The box on which SQL Server is installed is on a workgroup on the domain and the SQL Server is started up by the Local System user on the box.
I am thinking this has to do with windows security based on all the information that I have read on these kind of error messages. Any input on resolving this will be much appreciated.
Thanks,
MShah
View 1 Replies
View Related
May 8, 2006
We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.
I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.
In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.
Has anyone experienced this issue? Any inputs will be appreciated.
Just as an fyi, the excel file has a lot of vlookups.
Thanks,
MShah
View 1 Replies
View Related
