April 10, 2012 at 7:44 am
I have a SSIS package that needs to write to a share on a server separate from where the package is executing. I can execute the package from the command line successfully, but when we use a scheduling software, the package fails with a "connection is not valid" error. I am using the same username to execute through the scheduling software and on the remote server via command line. I still think there is a permission issue somewhere, but I don't have any idea where, since it works fine for me via command line. I looked in the event viewer logs and don't see any issues.
Anyone have any ideas?
April 10, 2012 at 8:04 am
DBAgal (4/10/2012)
I have a SSIS package that needs to write to a share on a server separate from where the package is executing. I can execute the package from the command line successfully, but when we use a scheduling software, the package fails with a "connection is not valid" error. I am using the same username to execute through the scheduling software and on the remote server via command line. I still think there is a permission issue somewhere, but I don't have any idea where, since it works fine for me via command line. I looked in the event viewer logs and don't see any issues.Anyone have any ideas?
What do you mean by "..and on the remote server via command line"?
Are you using a UNC path or a mapped drive? UNC paths are generally a better option.
Why are you not using SQL Agent to execute the package?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 10, 2012 at 8:48 am
Phil Parkin (4/10/2012)
DBAgal (4/10/2012)
I have a SSIS package that needs to write to a share on a server separate from where the package is executing. I can execute the package from the command line successfully, but when we use a scheduling software, the package fails with a "connection is not valid" error. I am using the same username to execute through the scheduling software and on the remote server via command line. I still think there is a permission issue somewhere, but I don't have any idea where, since it works fine for me via command line. I looked in the event viewer logs and don't see any issues.Anyone have any ideas?
What do you mean by "..and on the remote server via command line"?
Are you using a UNC path or a mapped drive? UNC paths are generally a better option.
Why are you not using SQL Agent to execute the package?
What do you mean by "..and on the remote server via command line"? - I log onto the server, open a command prompt, and paste: "D:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Server server\instance /SQL \PackageName
Are you using a UNC path or a mapped drive? UNC paths are generally a better option. - I am using UNC: \\server\share\FileName.txt
Why are you not using SQL Agent to execute the package? - There are other system dependencies on the package.
April 10, 2012 at 9:02 am
OK - do you have SSIS installed on both servers? Merely executing the command line is not sufficient.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 10, 2012 at 9:11 am
Phil Parkin (4/10/2012)
OK - do you have SSIS installed on both servers? Merely executing the command line is not sufficient.
Yes, SSIS is installed on both servers, but the command line is not the problem. The scheduling software is the problem. It can handle writing output files to the drive on the same server, but it cannot write output files to a network share.
For example:
SSIS package executes and writes to X:Drive on server1 - Good
SSIS package executes on server1 and writes to network share on server2 - Bad
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply