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.
| Author |
Topic |
|
JBG1983
Starting Member
2 Posts |
 Posted - 2010-10-06 : 15:58:28
|
| I'm a beginner in SQL and am trying to automate a process to extract data to a file on a regular basis.1) I created an SSIS job to write database data to a .csv file, stored on P:\Storefront.csv (obviously a mapped drive) a) Right-clicked on database --> Tasks --> Export Data b) Left the defaults the same on the first page (Windows Auth) c) Destination: Flat File, File Name: P:\Storefront.csv, Format: Delimited, Text Qualifier: <none>, Column names exist in the first data row d) Write a query to specify the data to transfer (copy/pasted query into the next screen) e) Left the defaults on the next screen and the preview looked correct f) Executed and Saved the SSIS Package under SQL Server (vs File System), Encrypt sensitive data with user key g) Named the SSIS Package and attached a description, used Windows Auth again h) When it executed, everything worked correctly...as I would expect.2) Set up a SQL Server Agent job to have this Package run every morning at 4am a) Named the job, owner is a Windows Server Administrator (full rights) b) Created 1 step as Type: SQL Server Integration Services Package, Run as: SQL Agent Service Account (only option available) c) Under General, I chose SSIS Package Store for the Package Source and used Windows Auth, browsed for the path of the file (ie P:\Storefront.csv) d) Set up the schedule to run M-F at 4am and to Write to the Windows Application event log when it fails e) When the scheduled job runs, it fails everytime with the following message: Code: 0xC020200E Cannot open the datafile P:\Storefront.csvWhy will the job work if I run it manually but not when I schedule it?Thanks,Jeff |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-06 : 17:15:27
|
| use unc path instead of drive letter. then make sure sql agent account has permissions on the folder/file |
 |
|
|
JBG1983
Starting Member
2 Posts |
Posted - 2010-10-07 : 09:39:35
|
| UNC path worked, much appreciated Russel. |
|
|
|
|
|
|
|
|
SSIS Schedule Issues