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 12»»

SSIS : Cannot open the datafile Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2008 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2008 9:25 AM
Points: 2, Visits: 1

Hi,

If I run the package from BIDS, it works fine. If I run the package inside Management Studio it works when I run it as a package.

It does NOT run when I schedule the job.

Error: 2008-03-12 10:51:56.16
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [194]
Description: Cannot open the datafile "D:\old_timesheet_repos\TimeSheet\files\date.txt". End Error
Error: 2008-03-12 10:51:56.16
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "Flat File Destination" (194) failed the pre-execute phase and returned error code 0xC020200E. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:51:55 AM Finished: 10:51:56 AM Elapsed: 0.344 seconds.
The package execution failed. The step failed.,00:00:01,0,0,,,,0

Any idea?

Thanks in advance.
Eric.
Post #468194
Posted Wednesday, March 12, 2008 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2008 9:25 AM
Points: 2, Visits: 1
I was able to fix the issue by giving full control to the 'users' group to the text file.
Post #468218
Posted Thursday, May 21, 2009 8:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 1:02 AM
Points: 2, Visits: 54
Hi,

This issue normally comes up when the corporate policy hardens the servers thereby causing many security issues.
Many a times even granting full control to the user account under which the package runs, does not resolve the error. So what you will have to do is add the user under whose account the package runs to the destination servers "Power Users group". This will give some permissions to the user account and resolve the security issues.

Best Regards,
Hitesh
Post #721809
Posted Friday, August 16, 2013 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 11:09 PM
Points: 2, Visits: 28
Thank you, I came up with the same issue. but got it resolved by your post.
Thank you,
Anilkumar
Post #1484997
Posted Friday, August 16, 2013 12:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
anil.kumars13 (8/16/2013)
Thank you, I came up with the same issue. but got it resolved by your post.
Thank you,
Anilkumar


Remember that you now gave everyone full access to your data in the file.
Just sayin'...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1485002
Posted Friday, August 16, 2013 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 11:09 PM
Points: 2, Visits: 28
thanks for sharing the information

so, what we have to do to give access to particular user, who can run the job as well.

Thank you,
Anilkumar
Post #1485004
Posted Friday, August 16, 2013 1:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
anil.kumars13 (8/16/2013)
thanks for sharing the information

so, what we have to do to give access to particular user, who can run the job as well.

Thank you,
Anilkumar


Use proxies in the SQL Agent job.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1485012
Posted Friday, August 16, 2013 2:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 1:02 AM
Points: 2, Visits: 54
Koen Verbeeck (8/16/2013)
anil.kumars13 (8/16/2013)
Thank you, I came up with the same issue. but got it resolved by your post.
Thank you,
Anilkumar


Remember that you now gave everyone full access to your data in the file.
Just sayin'...



Joen, can you elaborate how does everyone get access to the data, when only the specific user under whose account the package is run is added to power Users group? Does'nt seem to make a point..
Post #1485033
Posted Friday, August 16, 2013 2:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
hitesh.ramchandani (8/16/2013)
Koen Verbeeck (8/16/2013)
anil.kumars13 (8/16/2013)
Thank you, I came up with the same issue. but got it resolved by your post.
Thank you,
Anilkumar


Remember that you now gave everyone full access to your data in the file.
Just sayin'...



Joen, can you elaborate how does everyone get access to the data, when only the specific user under whose account the package is run is added to power Users group? Does'nt seem to make a point..


I wasn't referring to your post, but to the post by Eric:


I was able to fix the issue by giving full control to the 'users' group to the text file.


Doing this gives everyone who is a user full access to the data in the file.
It's much saver to use proxies in the SQL Agent job and give that proxy account specific permissions on that file/directory.

Even adding the proxy account to the Power User group might be insecure, as you're giving that account potentially too much permissions.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1485037
Posted Wednesday, September 11, 2013 1:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
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 #1493874
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse