Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package into our SQL Server and Create a Job to run the Package, the package fails and I see the following error in the log:

Started:  3:20:01 PM  
Error: 2013-03-27 15:20:01.70     
Code: 0xC002F304     
Source: copy template File System Task     
Description:
An error occurred with the following error message: 
    "Could not find a part of the path 
    'P:\IT\PROVIDERS\PROVIDERS AUDIT\File
    Template\QnxtProviderAuditList_yyyymmdd.xlsx'."  
End Error  
DTExec:
The package execution returned DTSER_FAILURE (1).  
Started:  3:20:01 PM  
Finished: 3:20:01 PM  
Elapsed:  0.452 seconds.  
The package execution failed.  The step failed.

The file path in the error is the file that needs to be copied (sourceconnection).

I can copy the file path directly from the error and go to it (via windows explorer) on the physical SQL Server machine so I do not understand why the package is saying it could not find the Path.

Does anyone have any idea why this package would run fine on my local machine but fails with the above error when run from the SQL Server? P:\ is a shared drive which the SQL Server has access to so I doubt it's an access/privileges issue.

share|improve this question
1  
P: is not mapped on the SQL Server. Use the FQDN for the server instead of a drive letter, like: \\machine\share –  Max Vernon Mar 27 '13 at 19:56
1  
Check if SQL Agent account has access to the folder location or not ? Also, how are you running the package, is it running as SSIS task or command line task ? Check if the package is saved using "Encrypt All with password" or not ? ... just saw Max's comment to see if you can access using \\servername\IT\PROVIDERS\PROVIDERS AUDIT\File Template\QnxtProviderAuditList_yyyymmdd.xlsx or \\servername\p$\IT\PROVIDERS\PROVIDERS AUDIT\File Template\QnxtProviderAuditList_yyyymmdd.xlsx –  Kin Mar 27 '13 at 19:57
    
Thank you All \\servername instead of the windows mapped drive letter worked! –  JuanVelez Mar 28 '13 at 12:52
add comment

1 Answer 1

up vote 6 down vote accepted

Mapped drives, like P:\ in your case are dependent on the user logged in. Just because you can see the drive letter when logged into the server machine does not mean SQL Server can "see" the drive letter. If you specify the UNC (Universal Naming Convention) name in the path instead of using the drive letter, and the account SQL Server is using has access to the share and path, then it will work.

Instead of P:\mypath\myfile.txt use \\machine\share\mypath\myfile.txt

share|improve this answer
1  
+1 - also true for windows scheduled tasks and can cause issues there for the same reason –  JNK Mar 27 '13 at 20:52
1  
Shouldn't it work if he would map the shared folder as a drive using a scheduled job under a System user? I think that way it would be available to everyone at system startup. –  Marian Mar 27 '13 at 21:26
2  
@MaxVernon Just an idea, if he needs that drive for other stuff too (like sharing docs..etc). –  Marian Mar 27 '13 at 21:33
1  
If he had xp_cmdshell enabled on the instance, that could be used to create the mapped drive too. Again, this is overly complex in my opinion. –  Max Vernon Mar 27 '13 at 21:35
2  
Rather than relying on xp_cmdshell being enabled, we found we had the best results by having a predecessor step in our sql agent job that performed a OS job step that created the mapped drive and then a successor step which dropped the mapped drive. Seemed cleaner than having it in the package as you don't have to delay validation –  billinkc Mar 28 '13 at 16:10
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.