Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS 2008 R2 - Export data to Flat File in another

Author  Topic 

aestima
Starting Member

10 Posts

Posted - 2014-06-13 : 12:14:34
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-13 : 12:42:31
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 - 2014-06-13 : 13:24:48
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 - 2014-06-13 : 13:26:49
Regarding the above account is correct? That has to be another user account?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-13 : 13:29:09
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 - 2014-06-16 : 10:26:53
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

737 Posts

Posted - 2014-06-17 : 08:09:50
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 - 2014-06-25 : 09:33:33
I got after using domain user.

Thanks to all
Go to Top of Page
   

- Advertisement -