February 27, 2009 at 11:58 am
Hi gang,
I'm getting the error below from my SQL job when it's calling my SSIS package that dumps my data to a flat file. I build packages all day long that deliver to .xls with absolutely no issues, but when I have it delievr to a flat file, I get the following error:
Started: 10:19:48 AM Error: 2009-02-27 10:19:50.59 Code: 0xC001401E Source: XXX Weekly Nurse Report Connection manager "Flat File Connection Manager" Description: The file name "\\sql19p\Reports\PROD1\SSISTEST\XXX_Weekly_Nurse_Report.txt" specified in the connection was not valid. End Error Error: 2009-02-27 10:19:50.59 Code: 0xC001401D Source: XXX Weekly Nurse Report Description: Connection "Flat File Connection Manager" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:19:48 AM Finished: 10:19:50 AM Elapsed: 1.828 seconds. Process Exit Code 1. The step failed.,00:00:03,0,0,,,,0
I can run the package locally and have it deliver to that file share just fine, but when the SQl agaent runs it, the connection manager takes a dump. Any ideas what the casuse is for this?
February 27, 2009 at 12:12 pm
Most likely a permissions issue with the account used to start your SQL Agent service. When you write out your XLS files, are they dumped to the same directory?
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
February 27, 2009 at 12:17 pm
Thanks for the reply. yes, it is going to the same directory as my XLS files. With that said, could it still be a permission issue?
February 27, 2009 at 12:25 pm
When you dump the files to Excel, are you running the job manually, or is it being run as a scheduled job?
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
February 27, 2009 at 12:43 pm
I have job running the packages that deliver to Excel. I seem to be making some sort of progress now. I deleted the file from the share, and then fired off the job again and it worked, however if I run the job a 2nd time, and that file is still in the directory I get that invalid name error again. I guess I could add a job step to delete the file after it's been delivered and emailed to the client, but I dont want a work around as the solution. On a side note, I have the flat file connection manager configured to overwrite the data in the existing file.
February 27, 2009 at 7:58 pm
Check the folder permissions - it sounds like you have "write" but not "modify" permissions granted to the SQL Agent account. This could keep you from modifying an existing file, but would allow you to write one if it didn't exist.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply