SSIS package scheduled to run by SQL Server agent

[Nixx123]

Good Day
I wonder if you can assist ,
I have scheduled a SQL Server Agent Job to run at a specific time
However I keep on getting an error , please see attached
The Error referrers to “Cannot open the datafile "Y:\NicolTest\Adaptiv\CD_Adaptiv_BOND_FWD.22"
Firstly I have checked my permissions on SQL logins [OK]
Secondly I have checked my Permissions to the Target destination [OK]
The aim of this Job is to run an SQL Server Services Intergration package (s) which extract and create files in a specific Mapped Drive

ERROR CODE

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
08/22/2011 11:35:52,Citydealer_Adaptiv_Extracts_SSIS_OUT,Error,0,11384MODDEV002V,Citydealer_Adaptiv_Extracts_SSIS_OUT,(Job outcome),,The job failed.  The Job was invoked by User STANBIC\a106246.  The last step to run was step 1 (Run CD_Adpt_Bond_Fwd).,00:00:39,0,0,,,,0
08/22/2011 11:35:52,Citydealer_Adaptiv_Extracts_SSIS_OUT,Error,1,11384MODDEV002V,Citydealer_Adaptiv_Extracts_SSIS_OUT,Run CD_Adpt_Bond_Fwd,,Executed as user: NT AUTHORITY\LOCAL SERVICE. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:35:53 AM  Error: 2011-08-22 11:36:30.77     Code: 0xC020200E     Source: Header Flat File Destination [403]     Description: Cannot open the datafile "Y:\NicolTest\Adaptiv\CD_Adaptiv_BOND_FWD.22".  End Error  Error: 2011-08-22 11:36:30.77     Code: 0xC004701A     Source: Header DTS.Pipeline     Description: component "Flat File Destination" (403) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:35:53 AM  Finished: 11:36:30 AM  Elapsed:  37.734 seconds.  The package execution failed.  The step failed.,00:00:39,0,0,,,,0

Please can you perhaps advise

 

[Skittle]

I seem to recall having this many years ago with my backups.
I think the mapping will not exist as far as the SQL Agent session is concerned.

You could try using the actual share definition ( eg.\\MyComputer\SQLBackup\SQL2005 )to identify the location.







Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[SQLBill]

As Skittle says, it could be an issue with the mapping...try using the full path.

But it could also be permissions. The SSIS package probably isn't running as you. It most likely is running under whatever login the SQL Server Agent service runs as. Check the job step and see what the Run As is set for. The default is SQL Agent Service Account. Whatever the Run as: is set for needs to have access to that path.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Nixx123]

Hi
Many Thanks for the replies ,
After considering your suggestions i had a look at creating a proxy acc which took care of the error i was getting , also the job is now being scheduled !
Alas i face a new issue now , as part of my SSIS package it uses a Config file which specified the creation name of a FLAT ASCII file to be created and where as mentioned in my previous Post .
The error is as follows:

Executed as user: NT AUTHORITY\LOCAL SERVICE. ...5.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  9:12:14 AM  Error: 2011-09-05 09:12:46.79     Code: 0xC001401E [b]Source: CD_Adpt_Fx_Option  Connection manager "Data" Description: The file name "\\10.250.6.72\\Inbound\\NicolTest\\AdaptivCD_Adaptiv" specified in the connection was not valid.[/b]End Error  Error: 2011-09-05 09:12:46.81     Code: 0xC001401D     Source: CD_Adpt_Fx_Option      Description: Connection "Data" failed validation.  End Error  Error: 2011-09-05 09:12:46.82     Code: 0xC001401E     Source: CD_Adpt_Fx_Option Connection manager "Footer"     Description: The file name "\\10.250.6.72\\Inbound\\NicolTest\\AdaptivCD_Adaptiv" specified in the connection was not valid.  End Error  Error: 2011-09-05 09:12:46.84     Code: 0xC001401D     Source: CD_Adpt_Fx_Option      Description: Connection "Footer" failed validation.  End Error  Error: 2011-09-05 09:12:...  The package execution fa...  The step failed

I have noted that when changing the configured value in the Config XML file where it states the File name to be created and also the path of where to create the file , Imeeditaley thereafter the Step List/Job Step Properties/Data Sources TAB , Connection String values picked up by the schedular are changed .
Please note that the Path displayed on the error has double \\
and the path in my XML Config File has only 1 \

 

[fredericofonseca]

Does the user NT AUTHORITY\LOCAL SERVICE on that machine have authority to access your local machine? Unlikely it has.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

To be more specific

When you select a job to run under SQL Server Agent the permissions of that job as those of the Windows user under which the SQL Server agent runs.
By default this is NT AUTHORITY\LOCAL SERVICE which will never have access to do anything outside its own machine.

It is advisable to create a local or network user (if using a domain) and use that user access to the network shares/computers required by the jobs.

Such an user would normally be set up in a way that he can not login to the network.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Nixx123]

Hi
Sorry for the very very late reply but your suggestions and advise has bared fruit ,
Thanks again for your priceless assistance and help