I have followed the steps in BOL (not too complex really) to schedule a package in SQL Server
Agent.
My package reads out Data from the SQL Server and writes some log into a file. It is encrypted
by a password and is stored to the filesystem.
Executing the pkg via dtexec works fine.
Then I've created a new job with one step via SQL Server Management Studio, added
the package and password when prompted.
I took the command line params and used them with dtexec: It woked.
I started the job manually from SQL Server Management Studio by selecting "start job"
from the context menu.
But when I try to schedule the job nothing happens. The only trace I can find is a warning in
the SQL Server Agent Error Logs:
"[162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 2"
This is inherent when you configure a schedule to go off once.
I have a Local DTS Package which will execute fine...no problems. When I try to schedule it from my NT 4.0 Workstation to the NT 4.0 Server, I get the job scheduling dialog box...fill it out...click OK...and NOTHING.
No error message, nothing in the SQL error log, nothing in the NT logs, no job in the schedule! When I perform the same task at the server logged in as Administrator it schedules successfully.
I am having a strange error coming in SQL server 2005.
I had a DTS package developed in SQl server 2000. Then i had migrated that DTS package to SSIS package using Execute DTS 2000 package. The conversion is successfull.
Then when i manullt execute this package it runs successfully without giving any error. But then wheh i schedule a SQL server agent job for the same above SSIS package it fails giving me some cryptographic error as follows:
Message Executed as user: CATOS-CGDBTUW02SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" 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: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "SQLPassword" 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: 2008-02-20 23:00:01.00 Code: 0x00000000 Source: Execute DTS 2000 Package T... The package execution fa... The step failed.
Not sure why is this happening.
Manually running the SSIS package from Visual studio does not gives error, but the same SSIS package when is been schedule from SQL server 2005's SQL server agent job it fails giving above error.
Using the "set values" tab, I am trying to set the values of package variables to strings that contain spaces and/or dashes. If I run the package using the integrations services server, it runs perfectly. If I schedule the package on SQL Server agent, I get the following error.
"Executed as user: GBPSQLServer. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "-restofstring" is not valid. The command line parameters are invalid. The step failed."
Looking at the command line generated by the SQL Agent interface, the value is quoted using double quotes like this:
/SQL "PackageName" /SERVER SQL1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "Package.Variables[LogFolder].Value";"\remoteservernameinetpub he-restofstring estofstring" /REPORTING E
It seems that the command line parser is not recognising the quotes, or maybe the slashes are throwing it off, or sql server agent is doing something funny to the command line before it executes it.
Normally scheduling a job is a very elementary operation but for somehidden reason I've been unable to schedule a jobwhich runs on a 28 day cycle, even though I have at least 16 other jobsseperately scheduled to run on a 28 day cycle withdifferent start dates/times.My production SQL server is running SQL Server Enterprise Edition8.00.760(SP3) on a 2 Processor(4 virtual) MicrosoftWindows 2000 Server 5.0.2195After creating my job through the New Job Wizard in Enterprise Manager,which at this point is 43 steps, the first step ofwhich is a Transact-SQL Script, and the remaining 42 all OperatingSystem Command steps, I try to create the schedule byclicking on the Schedules tab. I click the New Schedule commandbutton, type in a schedule name, keep the option buttonschedule type default as Recurring, and then click the change commandbutton. In the Edit Recurring Job Schedule popupscreen I selected the Daily option button in the Occurs section, typedin 28 in the Daily section so it reads Every 28days, and in the Daily Frequency section set it to Occurs once at10:00PM. In the Duration section set the start date to5/23/2005 with No End Date. Clicked OK several times to complete thejob setup.When I went to see the Job under the SQL Server Agent - Jobs node, itshows next run date as todays date, regardless ofwhat day I set up the job. I learned the hard way that when I set thisjob up with a (Future) start date of 3/28/2005 thatit not only ran on 3/28/2005, but it ran every day 10PM since thenuntil I noticed it and disabled the job. Since then Ihave set up and scheduled several other jobs to run on a 28 day cyclein a similar way, both through the DTS job schedulingwizard and scheduling tab when creating new jobs, and they all ran onthe desired schedule.I've been trying to manipulate this job schedule in various ways, evendeleting and recreating a new schedule for this jobthrough the GUI in Enterprise Manager and still encounter this problem.Now when I set up this job with the (already past)start date of 5/23/2005 I would expect it to indicate that the next rundate would be 6/20/2005 10PM, 28 days after5/23/2005 10PM. The run duration of this job is between 7 and 8 hoursdepending on processor load. But still it alwayssays the next run date is the day that I set up the schedule.I've created one step dummy jobs in hopes that I can give it thedesired schedule and then use this schedule for the realjob, but I cannot even give this dummy job the desired schedule.I compared this job schedule with the other 28-day jobs that run asdesired by first looking in the sysjobschedules table.Aside from the different start date/times the only difference was thatthe freq_relative_interval was set to 0 instead of 1like all the others. Changing it to 1 through a query did not fix theproblem, so I tried deleting the old schedule withsp_delete_jobschedule and creating a new schedule usingsp_add_jobschedule but got the same results. Next run date wasstill today. I tried modifying an existing schedule withsp_update_jobschedule so that freq_relative_interval insysjobschedules was correct, but still the next run date was today10PM.I noticed in the sp_update_jobschedule there is some code which iscommented " Notify SQLServerAgent of the change, butonly if we know the job has been cached", which checks if the job iscached? and then calls sp_sqlagent_notify. I evencalled sp_sqlagent_notify seperately from sp_update_jobschedule and thejob still shows today 10PM as the next run date.The sp_update_jobschedule checks the sysjobservers table for this job,so I deleted that row from the table and triedrescheduling the job again through the various methods described above.The row in sysjobservers was created by defaultduring this process, but the next run date of my job still shows todaysdate (I hit refresh every time I've checked).The only differences I can tell you between all the 28-day jobs thatwork, and this one is that this job will run for 7-8hours while others take minutes or seconds to run. Are there any othersystem tables to check besides sysjobschedules,sysjobs, or sysjobservers which are failing to be updated that arecausing my job schedule to be cached? Is it the RunDuration of this job which does not allow it to be on the 28-day cycle?I've exhausted all options. Any help would be greatly appreciated.Thanks,Mike OrlandoCAMP Systems
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:
I have a SSIS package that reads the data from an Excel file using an Excel Connection Manager and imports the data to a table on a SQL Server 2005 DB instance.
When I run this package locally on the server the package being on the file system, the package executes perfectly. Now I upload the package to the msdb database and the run the package from there and the package still executes successfully.
Now I schedule the package to run as a SQL Server Agent job and the package fails and when the logging is enabled I see this in the log file;
OnError,WEB-INTSQL,NT AUTHORITYSYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
OnError,WEB-INTSQL,NT AUTHORITYSYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1073450985,0x,component "Allocation Comments" (1) failed validation and returned error code 0xC020801C.
I am wondering why the AcquireConnection method call is failing when the package is scheduled? I am running the step as a SQL Agent Service Account and it is the Local System account that starts up the SQL Server Agent and Server and is an Administrator on the box.
I have created a DTS Package which collects data from DB2 and stores in a table on SQL Server v 7.0. The execution of the package works fine.
When I schedule the package by right-clicking and selecting SCHEDULE PACKAGE, I fill in the dialog box. When the package runs, I receive the following error: DTSRun: Loading... Error: -2147217390 (80041012); Provider Error: 0 (0) Error string: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). Error source: Help file: Help context: 0. Process Exit Code 1. The step failed.
I get this same error when I run the package as a job. The package is in the repository. My run command looks like: DTSRun /S 111.11.11.11 /U sa /P xxxx /N DTS_Projections /R The package owner is sa.
I created a simple test DTS package to import a text file (2 rows, 5 fields wide) into a test table. When I manually execute the package in DTS, it is successful. When I schedule the package and the text file is located on the local drive (C:) the package is successful. I then modified the package to import the same file from the network drives, manually executing the import the package is successful. But when I schedule this package to import from a network drive it fails. Help? Where could the problem come from? NT security, limitations in SQL Server 7 SW, different levels of security between manual and scheduled processes, network security?
FYI: I am running Server 7, on WIN NT Workstation 4.0, SP5, DB size 12 GB, rolling 90 days of data.
HAVE CREATED LOCAL DTS PACKAGES WHICH I AM ABLE TO SCHEDULE OK. MY PROBLEM IS I HAVE A DTS PACKAGE WHIC IMPORTS DATA FROM REMOTE DATABASE, THIS PACKAGE WILL RUN WHEN EXECUTED BUT I CANNOT SCHEDULE IT. ANY IDEAS PLEASE THANKS IN ADVANCE PHILL
I got a DTS package and when i schedule it to run every day it is failing....??
When i normally run the DTS it is executing but when i click on the JOB and start the DTS it is failing.... is there any permission issues i should look at while shceduling a Job..???? :confused:
I have a DTS package which populates tables in a database from text files.
This is scheduled to run at 8:30am every morning. I did this by right-clicking on the package and selecting "Schedule Package". I can see the task has been set up in SQL Server Agent's job list.
This task only runs for the first time, and not any further. This task runs "successfully" the first time, i.e the tables in the database are populated with records from a text file. However, when I click on job history for the job, it says there is no job history. The status on the job says "Executing Job Step '1 - ....".
I believe the first time the job was executed, it was not completed properly, hence the status has not been set to "Not Running".
The owner of the package is administrator on the server, therefore have full access to the server. The task was scheduled by the administrator too.
Does anyone have any suggestions on how I can fix this?
I am using Enterprise manager with an MSDE engine. I have created a DTS package that updates a table in one of the databases. If I right click and choose "Execute Package" it runs...No sweat. When I try to use the scheduler to run the job every hour, it always fails between 1 and 10 seconds into the job...It only returns the error "Failed During Step 1."
I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?
I have setup a DTS Package using ActiveX to update some information via the SQL DB.
When i execute the DTS Package manually by right clicking on it and clicking on Execute Package, it works fine everytime.
When I try to schedule the package to run twice a day automatically, when the package starts, it executes once, however the status still shows executing and the next run time is listed as unknown. It seems to hang up and never finish. I do have the Step to quit after success and it still seems not to quit.
Any suggestions. This is the first DTS package I have ever used. Thanks for any help
I am having a slight problem with building a SSIS package and then scheduling it to run.
Basically I have created a .dtsx package and gone to Build Package and I can find it anywhare in SSMS. This is the first time I have created one of these so I apologise if thi sis a really stupid question.
I have created a DTS package that imports a comma delimited (CSV) textfile into a table. One field uses a VB script that parses a date intothe proper format for the transformation. All other fields are copiedas is. When I run the package directly the date field is importedcorrectly (ie MM/DD/YYY). When I schedule the package to run (the jobis in SQL Server Agent, Jobs) the month and day entries aretransposed. In other words the date field is imported as DD/MM/YYYY(ie Dec 4, 2003 should be 12/4/2003 but the scheduled job imports itas 4/12/2003)Does anyone have a fix for this ?Thanks
If I am scheduling a DTS package from a client workstation, do I need a particular type of authority on the server? I'm asking because I was able to schedule packages, now I cannot and have made no changes on the client.
I assume since packages move int SQL Server Agent there is some authority required. We are using NT Authentication (SQL7/NT Server).
I have the following problem with a local dts package which I created with a domain adminuser (domainadmdba) and runs ms oledb for oracle to execute access for a oracle database. thereafter the data fills up into a sqlserver table, this runs as local package perfectly. if i place the package into the scheduler and run the dts-job with an another domain user (domainsrv_sql=service account) i get connecting problems. the service account (domainsrv_sql) has sysadmin right in sqlserver and local administrator right on the operating system. which right must the service account possess, so that he has the necessary right to execute this dts package?
single dump from the log file:
with admdba:
The execution of the following DTS Package succeeded:
Step 'Delete from Table [HELPDESK].[dbo].[cuspers] Step' succeeded Step 'Copy Data from Results to [HELPDESK].[dbo].[cuspers] Step' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:Unspecified error (Microsoft OLE DB Provider for Oracle (80004005): The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.) Step Error code: 80074005 Step Error Help File:sqldts.hlp Step Error Help Context ID:1100
First time shopper on this board...hopefully you can help me.
I am trying to figure out how to schedule a DTS package located on a Server to run at a certain time. The DTS package will contain the path to a V/B 6 executable file residing on the same server.
So to test my methodology, I made a package on a server with code to run the V/B 6 executable off of my local machine. When executing the package manually it works fine. However, when I schedule it the job does not start. I could not find a way to edit the job I had previously scheduled so I made another one in case I "fat fingered" something. The same thing happens...no job starts.
I'm new in SSIS Development. I have manage to design and deploy my Package to production SQL(MSDB) Security rely on server storage.
The problem start when I start scheduling in SQL JOB I'm getting the following error.
Executed as user: UCC-SQLSYSTEM. ...ion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:57:01 AM Error: 2008-05-12 08:57:02.08 Code: 0xC0202009 Source: Test_deployment_Table Connection manager "BLAKE-DBN12.Staging" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'BLAKEUCC-SQL$'.". End Error Error: 2008-05-12 08:57:02.08 Code: 0xC020801C Source: Data Flow Task Test_Deployment [97] ... The package execution fa... The step failed.
I have created a SSIS Package to complete various tasks and I have managed to deploy it on SQL, however I can't find it in the Management Studio. I just want to be able to schedule this package to run as a job.
I have a legacy DTS package on my test SQL Server 2005 in the ManagementLegacyData Transformation Services folder. I can run the package, but how can I schedule it?? this doesn't appear to be an option anymore like it was in 2000.
I have a problem in getting the info about the execution of a DTS Package.
I have a SQL-2000 Client installation on my Desktop to connect to SQL-2000 Server Database system to do all of my work. i had a DTS package stored in my SQL Server2000 Local Packages. Now i have to schedule the DTS Package from my Desktop. And the DTS package is processing some files from a shared network folder. when i used to run the DTS package manually from my desktop my desktop login have access to that shared folder so there is no problem to run the package.
suppose i scheduled the DTS to execute on daily basis at 10:00 AM. Now my doubt is wher the DTS will be executed after scheduling, which starts DTS execution and is it required my Desktop to be up daily at 10:00 AM, and how the network shared folder is going to be accessed by the DTS, means where to specify the authentication to the shared folder.
I am trying to scheduling package using "Executing Sql Server Agent Task" before that I create new job,But there is error in STEP "Command Line Parameter are Invalid"
I want to schedule an SSIS package to run always. So I tried scheduling the SSIS Package as a Job in the SQL Server Agent and I have set the Schedule Type as "Start automatically when SQL Server Agent Starts".
I have used an WMI Event Watcher task to raise an event when the file dropped in a folder.
And I have provided the configuration file also properly in the job. But still my package is not running properly. The application is running, but it is not raising any events even when the files are dropped in that folder.
I have a package that I have set the protection level to EncryptSensitiveWithPassword. How do I setup SQL Agent to run this package. I have checked books online and google and can't find a decent article that walks you through the steps of setting it up...specifically how to use the /DECRYPT switch with sql agent.
Hi I have a strange scheduling problem. I have a job, i test it through BIDS - Works I test it through SSMS - works I run the job - Works I schedule for a run time job - Works I schedule for a recurring job - Works
However (here is the really annoying part) it fails to run at the night run (11.20PM) there are 6 other packages that run fine after it , 11.30 , 1AM .. 4 AM etc ...
So last night I changed the package to run at a later time, and it worked, but the other package that ran 2nd (which now ran 1st) failed !!
its almost as if the 1st package has to fail !!!!! When i look at the history and detailed log all it says is "The step Failed"
anyway , I can set up a workaround by starting a dummy job that is supposed to fail..
FYI the jobs are run under administrator and they are protected by a password (encryptSensitiveWithpassword) .
