SSIS Permission - Write File to Shared Folder on Another Server

Question

SSIS Permission - Write File to Shared Folder on Another Server

SSC Guru

Points: 55548

February 2, 2022 at 10:32 pm

I have an SSIS package that was writing to a local folder on the SQL server for testing, now trying to write it to a production folder on another server. I made the folder shared to "everyone" but even that doesn't work.

Within the package, I was able to browse to the remote folder, so the SQL server can "see" it, but I get an error when trying to run the SSIS from a query window.

Microsoft (R) SQL Server Execute Package Utility
Version 15.0.4188.2 for 32-bit
Copyright (C) 2019 Microsoft. All rights reserved.
NULL
Started:  5:30:15 PM
Error: 2022-02-02 17:30:16.34
   Code: 0xC0202009
   Source: Warehouse Data Export Connection manager "DestinationConnectionExcel"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "The Microsoft Jet database engine cannot open the file '\My_Server\d$\Warehouse_Location_Export\Warehouse Data Export.xls'.  It is already opened
 exclusively by another user, or you need permission to view its data.".
End Error
Error: 2022-02-02 17:30:16.34
   Code: 0xC00291EC
   Source: Preparation SQL Task BP Whse Execute SQL Task
   Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  5:30:15 PM
Finished: 5:30:16 PM
Elapsed:  0.953 seconds
NULL

February 4, 2022 at 5:04 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

that happens a lot and sometimes without any explanation.

copy the remote file locally, change it as required and then copy it back to destination.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Thanks, I used SSIS to write file locally, then DOS COPY in a .BAT file to move it to the remote server.

This reply was modified 2 years, 9 months ago by homebrew01.
This reply was modified 2 years, 9 months ago by homebrew01.
This reply was modified 2 years, 9 months ago by homebrew01.

will299 Newbie Points: 2 More actions · Answer 3

will299

Newbie

Points: 2

February 4, 2022 at 5:05 pm

#3980762

Good one keep it up

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

will299 wrote:

Good one keep it up

Really? What was good about this thread? I could be wrong but I'm thinking that you're just doing a SPAM test. If I am wrong about that, then welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

homebrew01 wrote:

Thanks, I used SSIS to write file locally, then DOS COPY in a .BAT file to move it to the remote server.

Just curious - why not use the built-in file system task, or even a script task to copy the file? I normally just use a script task - much easier to copy/rename/move/delete files and generally only takes a line or two of code.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

Jeff Moden wrote:

will299 wrote:
Good one keep it up
Really? What was good about this thread? I could be wrong but I'm thinking that you're just doing a SPAM test. If I am wrong about that, then welcome aboard.

its a spammer - first post has already been removed

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Jeffrey Williams wrote:

homebrew01 wrote:
Thanks, I used SSIS to write file locally, then DOS COPY in a .BAT file to move it to the remote server.
Just curious - why not use the built-in file system task, or even a script task to copy the file? I normally just use a script task - much easier to copy/rename/move/delete files and generally only takes a line or two of code.

No good reason.