SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS 2008 R2 - Export data to Flat File in another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aestima
Starting Member

10 Posts

Posted - 06/13/2014 :  12:14:34  Show Profile  Reply with Quote
Hello Everybody,

I'm trying to export data from a table in database server to flat file in another server, however it is happening the error Access Denied. I'm using SQL Server Integration Service 2008 R2 to do this.

I've checked all the ways that's happening could the level of security and could not resolve.

Please could someone tell me what I'm missing in this case?

Following error log:

SSIS package "AtualizarDados.dtsx" starting.
Information: 0x4004300A at Gerar arquivo, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Gerar arquivo, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Gerar arquivo, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Gerar arquivo, Geração de Arquivo [63]: The processing of file "\\targetserver\Export_File\Export_File.txt" has started.
Warning: 0x80070005 at Gerar arquivo, Geração de Arquivo [63]: Access is denied.
Error: 0xC020200E at Gerar arquivo, Geração de Arquivo [63]: Cannot open the datafile "\\targetserver\Export_File\Export_File.txt".
Error: 0xC004701A at Gerar arquivo, SSIS.Pipeline: component "Geração de Arquivo" (63) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x40043008 at Gerar arquivo, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Gerar arquivo para DMZ, Geração de Arquivo [63]: The processing of file "\\targetserver\Export_File\Export_File.txt" has ended.
Information: 0x4004300B at Gerar arquivo, SSIS.Pipeline: "component "Geração de Arquivo" (63)" wrote 0 rows.
Information: 0x40043009 at Gerar arquivo, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Gerar arquivo
SSIS package "AtualizarDados.dtsx" finished: Success.


Regards,

Antonio Estima

gbritton
Flowing Fount of Yak Knowledge

1530 Posts

Posted - 06/13/2014 :  12:42:31  Show Profile  Reply with Quote
Does the account running the dtsx (e.g. SQL Server Agent) have write access to the destination location?
Go to Top of Page

aestima
Starting Member

10 Posts

Posted - 06/13/2014 :  13:24:48  Show Profile  Reply with Quote
Hi gbritton,

The account in source server is NT AUTHORITY\NETWORKSERVICE that is running the SQL Server Agent sevice and SQL Server Integration service and it has permission in the destination location with NETWORK SERVICE system operation user.
Go to Top of Page

aestima
Starting Member

10 Posts

Posted - 06/13/2014 :  13:26:49  Show Profile  Reply with Quote
Regarding the above account is correct? That has to be another user account?
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1530 Posts

Posted - 06/13/2014 :  13:29:09  Show Profile  Reply with Quote
Usually you would need an AD account to run the job (perhaps as a SQL Agent proxy) with permissions in the target folder.
Go to Top of Page

aestima
Starting Member

10 Posts

Posted - 06/16/2014 :  10:26:53  Show Profile  Reply with Quote
Hi gbritton,

I have put an AD account with administratives permissios to running SQL Agent and SQL Server Integration Services. Finally, I have created a proxy with this user and the error message keeps popping up
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
676 Posts

Posted - 06/17/2014 :  08:09:50  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
does the account have write permission on the folder \\targetserver\Export_File\Export_File.txt

Javeed Ahmed
Go to Top of Page

aestima
Starting Member

10 Posts

Posted - 06/25/2014 :  09:33:33  Show Profile  Reply with Quote
I got after using domain user.

Thanks to all
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000