SSIS package runs ok in BIDS, fails when running from SQL Server Agent

Question

Post reply

SSIS package runs ok in BIDS, fails when running from SQL Server Agent

manuelroman98

Mr or Mrs. 500

Points: 593
More actions
March 10, 2010 at 1:39 pm

#212364

Hi forum,
I am trying to make sure an SSIS package runs on SQL Server Agent and i am getting nowhere.
It run ok in BIDS, but i am getting error when i do it from SQL Server Agent.
Errors:
Code: 0xC001401E Source: ProcessBankFile Connection manager "Flat File Destination Manager"
Code: 0xC001401D Source: ProcessBankFile Description: Connection "Flat File Destination Manager" failed validation.
Path is: \\servername\import_folder\filename
What i am trying to do is run this packeage form a remote server (my next question), but i am trying to make sure that it works in SQL Serve Agent first.
Any suggestions?
Regards,
Manuel Roman

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Has the SQL Server Job Agent account access to the specified path?

Maybe he hasn't the permission to read files in that folder.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 2

i didn't know that SQL Server agent needed to have permissions for that.

How can i find out?

Regards,

Manuel

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Check the security settings on your remote share to see if the SQL Server Agent account has the access you need.

Shamless self promotion - read my blog http://sirsql.net

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 4

And how do i do that?

I don't know where to look for security settings for my remote share.

We have 3 severs: 1 app server, 1 terminal services and 1 SQL server.

i can exceute the package from within SSIS... (in SQL SERVER)

i cannot excecute the package from SQL Server Agent (same SQL Server).

In the package i need to access fodlers from the App Server, is that what i need to make sure i have access to?

Bottom line is that i need to execute the job from the App server, but that is a question for later....

Regards,

Manuel

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 5

Go to SQL Server Configuration Manager and see which account the SQL Server Agent uses.

Then make sure that this account has read (and if needed, write) permissions on the folders of your App server.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 6

in The properties tab the Built-in Account is Local System.

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 7

The Apps folder has permissions for 'Everyone'

That should be enough right?

Regards,

Manuel

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 8

Although it is not very secure, that should do it.

Microsoft discourages the use of Local System as account though:

http://msdn.microsoft.com/en-us/library/ms191543.aspx

What is the specific error that you get when you try to run the package? Only that the Destination Manager failed validation?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 9

Got it.

I've talked to the Administrator, they are going to change it this weekend, thanks for the tip.

The error reads:

Error: 2010-03-12 10:05:23.12 Code: 0xC001401E Source: ProcessBankFile Connection manager "Flat File Destination Manager" Description: The file name "\\server_name\export\BANK IMPORTS\Export to SAP\BANK_FILE.csv" specified in the connection was not valid. End Error Error: 2010-03-12 10:05:23.12 Code: 0xC001401D Source: ProcessBankFile Description: Connection "Flat File Destination Manager" failed validation. End Error Error: 2010-03-12 10:05:23.12 Code: 0xC001401E Source: ProcessBankFile Connection manager "O0195" Description: The file name "\\server_name\export\BANK IMPORTS\BANK FILES ARCHIVE\O01952010-03-12.RCN" specified in the connection was not valid. End Error Error: 2010-03-12 10:05:23.12 Code: 0xC001401D Source: ProcessBankFile Description: Connection... The package execution failed

Regards,

Manuel

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 10

You have SQL Agent setup to use the localsystem account which does not have access to remote resources (ie the share on the app server).

You'll need to change the Agent account to a domain account which has access or setup a proxy and execute the job step using that http://msdn.microsoft.com/en-us/library/ms189064.aspx

Shamless self promotion - read my blog http://sirsql.net

manuelroman98 Mr or Mrs. 500 Points: 593 More actions · Answer 11

This is the command code from the SQL Server Agent job:

/FILE "C:\Documents and Settings\mroman\My Documents\Visual Studio 2005\Projects\bank_file\bank_file\ProcessBankFile.dtsx" /CONNECTION "Flat File Destination Manager";"\\ServerB\export\BANK IMPORTS\Export to SAP\BANK_FILE.csv" /CONNECTION O0195;"\\D1rv210b\export\BANK IMPORTS\BANK FILES ARCHIVE\O01952010-03-12.RCN" /CONNECTION SourceFlatFile;"\\ServerB\export\BANK IMPORTS\O0195.RCN" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Regards,

Manuel