September 11, 2013 at 1:26 pm
Hi All,
I have a SSIS package which get the result from a query and dumps that in a TXT file.
It runs well in BIDS. But when I scheduled this as SQL Agent job it fails saying "Cannot open the datafile "C:\SSIS\File_09112013.txt". End Error Code: 0xC004701A "
I have a variable which holds the filename "C:\SSIS\File_09112013.txt".
What is the solution for this?
Please suggest.
Thanks
September 11, 2013 at 1:40 pm
ensure that the file exists in that location on the server or pass in correct location from server standpoint.
September 11, 2013 at 1:53 pm
herladygeekedness (9/11/2013)
ensure that the file exists in that location on the server or pass in correct location from server standpoint.
+1
Does the c:\SSIS folder exist on the server?
September 11, 2013 at 4:06 pm
I have deployed the package in my local file system and the file does exist.
Also the filename is stored in a variable and evaluated as expression:
Variable name Expression
DEST_FILE : "C:\\IPC_" +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252)
DatePart("d",getdate()),2) +(DT_STR,4,1252) DatePart("yyyy",getdate()) + ".txt"
Even if the file doesn't exist it should create it dynamically. Please correct me if I am wrong.
And I have given the permission to the proxy account that runs this SSIS package as Job.
But even then I am getting the error.
September 11, 2013 at 11:38 pm
nishav2 (9/11/2013)
I have deployed the package in my local file system and the file does exist.Also the filename is stored in a variable and evaluated as expression:
Variable name Expression
DEST_FILE : "C:\\IPC_" +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252)
DatePart("d",getdate()),2) +(DT_STR,4,1252) DatePart("yyyy",getdate()) + ".txt"
Even if the file doesn't exist it should create it dynamically. Please correct me if I am wrong.
And I have given the permission to the proxy account that runs this SSIS package as Job.
But even then I am getting the error.
Even though SSIS will create the file, it won't create the folder if it does not exist (at least I think this is true) - hence my question, which you have not answered.
September 11, 2013 at 11:49 pm
Yes you are correct. I mentioned the path wrongly. It's only C:/
No folder.
September 11, 2013 at 11:50 pm
So why is it saying 'Cannot open the data file" ?
September 12, 2013 at 12:23 am
nishav2 (9/11/2013)
So why is it saying 'Cannot open the data file" ?
Many servers have an additional layer of security which prevents file creation at the root of the c: drive - and this may be an issue in your case.
I suggest you try a lower path - c:\SSIS\DataFiles or whatever - just to rule this out.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy