.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS Jobs VS SQL Server Job Agent

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.


View Complete Post

More Related Resource Links

SSIS package fails under SQL Server Agent job


Hi All

I have designed a SSIS package to encrypt a file using Cipher (EFS) it works fine from BIDS & Package Execution Utility.

But SQL Server Agent job is failing to do this. I am getting the error as

" In executing C:\Windows\System32\Cipher.exe /e /a C:\Test.txt at "",The Process Exit code was 1 while the expected was 0." 


Please help me.




Sql Server Agent stops executing jobs


Hello everyone.  I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule.  I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again.  It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed.  Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:


SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed.  Unable to determine if the owner (AD\adacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).


Any help would be greatly appreciated.

sending mail in SQL using SQL Server Agent Jobs



 I am using SQL Server 2008 and I want to create one job in that.

 Job should be like that it will run any T-SQL and the output of that query

should be mail to me. But I am not able to do this.

Please help me...

SSIS package on standalone server invoked by SQL Server Agent raises DTS_E_PRODUCTLEVELTOLOW


I have developed an SSIS 2005 package but the client was reluctant to install SSIS on their SQL Server and instead installed it (SSIS) on an IIS server which doesn't have SQL Server on it all. Microsoft documentation surprisingly indicated this would work. http://technet.microsoft.com/en-gb/library/cc966389.aspx states "SSIS packages can also be run on systems where SQL Server is not installed. However, the .NET Framework and the SSIS runtime must be installed in order to execute the packages on systems that do not have SQL Server 2005 installed."

The package can be run from within BIDS and by invoking from the SSIS server fine and runs to completion. It fails when invoked by the SQL Server Agent on the actual SQL Server (i.e. NOT the SSIS server) (which IS Enterprise version) and raises a DTS_E_PRODUCTLEVELTOLOW error. This is the third "step" out of three and is a fairly basic data flow task where the results of a SQL Server proceedure are output to a text file.

I have logging on which doesn't tell me anything more than I already knew
OnError,<ServerName>,<SQLServerAgentUser>,<PackageName>,{C88F42DE-E3A8-40CB-A140-F10E270C5F1F},{1D2B421E-E067-4458-A908-23C7F081EF28},9/30/2010 4:52:21 PM,9/30/2010 4:52:21 PM,-1073450754,0x,

SQL Agent - Running SSIS Package - Windows Server 2008/Windows 7 - Messenger Services


We have tried to run a SQL job for SSIS Package. But it failed during the execution with the error "[364] The Messenger service has not been started - NetSend notifications will not be sent". But in the job, no notifications have been configured and in windows server 2008/Windows-7 there is no messenger service at all.

When we search for the the messenger service in windows 7/windows server 2008, we found that service is not in both the OS and it was replaced by msg.exe. 

Any help or workaround would be appreciated.

Thanks in advance.

PS: The normal SQL jobs other than SSIS are working fine without any error.

Vijay Pandurangan

SSIS package which reads excel files fails on SQL Server Agent



I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?



Migrating SQL Server 2005 databases, including SQL Agent jobs and SQL users



I should begin to migrate all my data from a SQL Server 2005 (x86) SP3 to another server with SQL 2005 (x64) SP3 installed. About moving the Databases, I think that detach and attach them won't create unexpected problems.

The problems that scare me are:

  1. Migrate the SQL Agent processes. I've created so complex jobs that I'd rather swim across an ocean than rewrite them.
  2. Migrate Users. There are near 70 users with integrated security and each has different mappings to the 40 DBs. Unfortunately there's no domain, so I don't know whether it's possible to copy them without changing their GUIDs.

Does anybody know if my goal is within reach?

Can someone tell me which step to make (and in what order)?

Thank you very much.

SSIS SQL Server Agent Job Schedule


We have a job that is using a foreach loop to grab input from a text file, perform some transformations and place it into a SQL Server database using OLE DB providers.  The package works fine from the local environment (within Visual Studio).  Then when you import the package from the file system into the MSDB package store, it works fine from there as well.

The problem is when we try to schedule the job using the SQL Server Agent.  It is a one step job and it says its successful when we run it, but it doesn't put any information into the database itself.  We have other packages running that are working that run from the package store and take information from one database and put it into the same SQL Server DB we are trying to put our text file information into.

Has anyone encountered the problem where the package works from the file system as well as the package store, but doesn't work when you schedule it as a job?

Errors when using Proxy/Credentials to execute SQL Agent jobs that access SSIS packages


Running SQL Server 2008, on Windows Server 2008.

I'm having a problem trying to execute a job.  The job has executed successfully when I configure the SQL Agent account (domain account with sysadmin permissions in the instance) as the owner/executor of the job.  However, I want to tighten down security so I'm trying to use a proxy/credential.

Here are the steps I've followed:

1) create a non-privileged SQL account called "SQLJobOwner" that owns the job.

2) create a stored credential (domain account) that has an account on the local machine where the instance resides, and has "log on as batch" permissions.

3) created a login to the instance for the domain account that is stored as a credential, and granted sysadmin permissions to the account.

3) create a new proxy called "SSISProxy", and granted access to the SSIS subsystem.

4) Added job owner "SQLJobOwner" as a principal in the SSISProxy configuration window.

5) changed the job step that executes the SSIS package to "Run as" the "SSISProxy".


When I execute the job, the job fails with the following error:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).  The step failed.

In addition, I get

Issue in SQL server agent job running SSIS package containing a batch script.


I have a package that I have created that runs locally and as package on the server that has a batch script with in it and it run correctly when manually run. But when I create a sql server agent job it runs the job but doesn't execute the package but returns with a successful run. I have setup a proxy account trying to run it with this. I have given the permission to the batch script to the file that the SQL service account. I have tried everything I can think of even setting the package creator to the same name as the service account in the package, and even doing a dtexec command from the SQL job. If I run the dtexec from a cmd prompt the package runs. Need help please thanks.



executing bat files in SQL Server Agent Jobs



there are 2 batfiles running in daily maintenance plans. one, compressing Backups using GZIP and the other deletes old Backup Files.

the delete.bat uses "forfiles" command and successfully deletes files. recenlt the maintenance plan continued to failed and to investigate i foundout that when there are no files met for  the bat files' criteria, it is being recorded as an error and job faliure in SQL Server.

for example, when there are no files to be deleted, the bat file returns a message like "ERROR NO FILES FOUND UNDER THE GIVEN CRITERIA"

this message is interpreted as an error in the SQL Server JOb which is actually not.


do i have a way of handling this kind of errors?


please reply




sql server agent - job schedule 22022 error


Hi ! I have scheduled a job in sql server 2008 to send birthday e-mails. I run the script and it looks wroking but in agent schedule it doesn't. I am getting the below error; what is the problem?

TITLE: Microsoft.SqlServer.Smo
Start failed for Job 'Sending_transferdb_birthdate_e-mails'. 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&Link

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" 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: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

Access to SQL Server Jobs

Hello folks, Would like to grant group of users ( windows logins) to CREATE / MODIFY / DELETE / SCHEDULE jobs on SQL Server 2005. But can not grant users SYSADMIN privileges. I know that by granting users SQLAgentOperatorRole, SQLAgenetReaderRole and SQLAgentUserRole the User will be able to create jobs but they will be the owner of the newly created job and other users can not MODIFY / VIEW / RESCHEDULE the jobs. Is there any way to customize permissions to windows logins to have permission to CREATE / MODIFY / DELETE / SCHEDULE jobs without granting them SYSADMIN privilages ? Appreciate your reply   Thank You Arunaroon

sql server agent roles and permissions

hi does any one have idea of what kind of roles are needed for creating a SQL server agent job and why those roles are needed?? Please Reply ASAP Because i don't have sysadmin rights.........so that other developers can use the same login for creating the jobs Thanks in advance

Error starting SQL Server Agent - Could not load the DLL xpstar90.dll. Reason: 126

AD Service account password was changed.  Now the SQL Server Agent (MSSQLSERVER) will not start even after updating the password.  Have no access into the databases.  Log files say "Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."  Service Account has Full rights on system.  Any help in solving the problem would be great.

Install on New Windows7 - Getting Invalid SQL Server Agent credentials

Hi: I have a Developer License for MSSQL Server 2008.  Have just upgraded to Windows 7 Professional Edition.  When installing MS SQL Server, the setup shows that I have an instance of SQL Server Express already installed.  I am given a choice between Default Instance and Named Instance.  First time I chose named instance and then used the name of my machine - RBS1.  I received the following message:  The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service. Went back and tried Default instance and got same answer.  Can't proceed to install.  Any help in this greatly appreciated. roger  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend