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: Wednesday, December 10, 2014 8:57 AM
Points: 43, Visits: 98
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 @ 2:31 AM
Points: 5,317, Visits: 12,358
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Wednesday, December 10, 2014 8:57 AM
Points: 43, Visits: 98
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 @ 2:31 AM
Points: 5,317, Visits: 12,358
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Wednesday, December 10, 2014 8:57 AM
Points: 43, Visits: 98
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: Wednesday, December 10, 2014 8:57 AM
Points: 43, Visits: 98
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 @ 2:31 AM
Points: 5,317, Visits: 12,358
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1494009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse