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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply