RUNNIG A JOB?

I HAVE A PROBLEM,, I``ve just deployed a package into my server..it runs without problems from my SSIS.. but when i set a job with the sql agent and the package to it,. the job just fails .. why? any solution?

please somebody help me!!!

yes, this question comes up a lot, I apologize for the confusion. We are working on a KB article to help explain issues around this. It should be ready within a few days. The short asnwer is that when you execute under agent, you are (miost likely) executing "as" a different user than when you execute in the dev studio or command line with dtexec.exe.

So the reason a different login may fail could be due to the ProtectionLevel property the package is set to AND/OR the user you execute with under agent does not have some permission needed, like writing to a certain file share where the package wants to dump a file.

You should read up on the SQL Server Agent proxy acccounts and the SSIS package protection level. Usual cause is the default protection level is encrypt sensitive with a user key, and if its a different user OR different machine you will not be able to load all of the properties in the package such as connection info. You could therefore create an agent proxy account to 'mimic' the same as the pacake author , or change to a password protection level and suppy the password in the Agent job, or change protection level to server storage which puts the package into SQL and you can control access via SQL DB roles rather than a specific user encryption key.

You may also search the forum for more on this as lots of helpful folks in this forum community have responded to similar questions and collectively there is a lot of info here.

Hope this helps

|||

i`ve tried all this options.. set the protection level to storage server when copying to the target server.. created an agent proxy account =this account has identity of an administrator in my server= after that i set the run as property of the job to this account and still won`t work

thanks for your help

|||This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.|||Ruk, as Micahel noted the KB article I referred to is now published. Please review the article. Based on experience, the article should cover all the possible cases that would result in the issue you seem to have, but if not then we will need a more information such as the messages returned to the SQL Agent Job history and error messages returned via SSIS Logging.|||

i've tried to catch the error by running each task of my dts separately.. then i found the one that is causin troubles.. it looks like by using data flow tasks something unknown happens.. i'm using an OLE DB source to retrieve some data from the db... netx step i use this data conversion task because some of the results of the query aren`t supported by mi destination which is an OLE DB destination that maps to an excel file in the target server.. this is weird cause i run this taks in my bids and runs ok.. i run this again from SSIS in the target server and runs ok.. but when i scheduled it in a job it just fails.... any solution?

thanks for your help!!!!

|||Have you followed the part of the KB article where it describes how to configure logging using CmdExec Agent subsystem, or SSIS logging? What are the errors reported?|||

these are some errors that the log provided

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
--

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXX\Administrator
Source Name: DESIE_CatalogoDeSucursales(Semanal)
Source ID: {D1025489-4A06-4347-9674-0A73A1847829}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Name: OnError
Message: component "OLE DB Destination" (40) failed validation and returned error code 0xC020801C.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1073450985
For more information, see Help and Support Center at

"Sucursales" is the name of the connection manager that maps the xls file destination. the job just fails when trying to validate it

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

|||

ruk_walled wrote:

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

Where is XLS file located: local disk, UNC network drive (\\machine\drive\file), mapped network drive?

When you've tried changing destination to flat file - was the CSV file in the same location as XLS? If not, try placing the CVS to the same folder (to check whether it is related to destination-type or the problem is with access to this location).

|||

the xls file is located in a local disk.... and the flat file is created in the same path with no problems,.. the problem is not at the moment of creating the file.. but at the moment of validate the connection manager that refers to this local path...when using xls

|||

I can't fit some parts together:

my destination which is an OLE DB destination that maps to an excel file in the target server|||

i'm sorry my english is not good enough

here is the thing.. i'm doing a report from some data and try to write it in an xls file.

i have a data flow task that connects to a db in the target server to retrieve the data. this uses an OLE DB connection manager which has the server name.. the db. and the user to connect. --this part parses ok.

since there are some data that doesn′t match with the predefined ones.. i use a data conversion transformation task

then..

i'm using an ole DB destination which uses an OLE DB connection manager that maps to the file that is located in a local disk on the target server.. the file exists and it contains only the headers .

the package is stored in the server.. so is the file destination. i also changed the OLE DB destination for an Excel Destination but i happened the same..

i copied this dts from mi computer to the target server using the option save a copy of. from the bids and setting the protection level to server storage.

when i schedule the job it seems to run but as the log shows.. something happens when trying to validate the connection manager that maps the path of the file.. but the path and the file exists...

|||

i have finally found the problem... all my dts were programmed under 32 bits environment ..<my target server is running under 64 bits so this was the problem.. that i'm using jet 4.0 which is not supported for 64bits so this is the answer:

thanks to michael Entin..

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"

|||

Craig,

Where is the KB Article? Is it at http://support.microsoft.com/kb/918760 b/c this was not helpful for the problem that I face.

|||

Hi,This is dinesh here.I need to create a job and schedule it in sql server 2005.But I dont find the sql server agent option under the database instance in the object explorer. I need to write a simple job and just schedule it. Do i need to instal some extra components of sql server 2005?

I would appreciate if you could help me.

sql