Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package fails when scheduled a SQL Agent job Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:54 AM
Points: 41, Visits: 93
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
Post #1493875
Posted Wednesday, September 11, 2013 1:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
ensure that the file exists in that location on the server or pass in correct location from server standpoint.
Post #1493880
Posted Wednesday, September 11, 2013 1:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,257, Visits: 12,183
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1493886
Posted Wednesday, September 11, 2013 4:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:54 AM
Points: 41, Visits: 93
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.
Post #1493936
Posted Wednesday, September 11, 2013 11:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,257, Visits: 12,183
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1494000
Posted Wednesday, September 11, 2013 11:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:54 AM
Points: 41, Visits: 93
Yes you are correct. I mentioned the path wrongly. It's only C:/
No folder.
Post #1494002
Posted Wednesday, September 11, 2013 11:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:54 AM
Points: 41, Visits: 93
So why is it saying 'Cannot open the data file" ?
Post #1494003
Posted Thursday, September 12, 2013 12:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,257, Visits: 12,183
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1494009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse