Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Errors Scheduling Jobs with SQL Server Agent

Author  Topic 

dgs009
Starting Member

3 Posts

Posted - 2014-03-24 : 15:39:22
I am having trouble assigning schedules to jobs through SQL Server Agent. The jobs are executing packages that I created inside Data Tools. One packages transfers data from a flat file into SQL Server. Another package exports data from SQL Server into a flat file. The packages work perfectly when I execute them manually. However, the jobs always fail when I assign them to a schedule.

I have tried two different storage methods: the File System and SSIS Catalog. Whenever I use a package that was stored on the file system, the error is “Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.” Whenever I use a package that was stored in the SSIS Catalog, the error is “Data Flow Task: Error: Cannot open the datafile “C\...” Data Flow Task: Error: Flat File Source failed the pre-execution phase and returned error code 0xC020200E.

I’m assuming my problem has something, if not everything, to do with permissions. My main reason for thinking this is that the packages work perfectly when I execute them manually. I’ve also tried using proxies but I get the same errors. Should I focus on fixing the “Cannot open datafile” issue since the packages seem to execute fine even though they are not in the proper XML format? If permissions are the issue, how do I go about granting the right users the appropriate access?

Thank you!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 08:27:37
Have you tried creating a DB Credential under Security then add that as a SQL Agent Operator and make sure you run the package with the new operator added to the SSIS Package Execution?

We are the creators of our own reality!
Go to Top of Page

dgs009
Starting Member

3 Posts

Posted - 2014-03-25 : 11:57:30
I have created a new credentaial. How would I go about adding that as a SQL Agent Operator? Also, I don't know which identity to use. None of the ones I have seem to have the correct permissions. How do I grant them windows users access?
Go to Top of Page
   

- Advertisement -