Accessing Directories on Remote Servers Using SSIS

November 4, 2022
|
SSIS

In this scenario, you have developed an SQL Server Integration Services(SSIS) package with SQL Server Data Tools (SSDT),  that accesses adirectory on a remote server, and performs some action on the file or fileswithin the directory. You build and execute the package on your device and itruns to successful completion, resulting in exactly what you expect.  Youpublish the job to SQL Server and it results in error alerting you to the factthat it is unable to access the resource you specified.

The issue is that the SQL Server agent you are running the package under,does not have the permissions needed to access the server resource. I willprovide you with one method for allowing the SSIS package to connect to aremote server.  You will need a user name and password for an account thatis able to access the folder you wish to connect to, typically, an account withread/write access.

Step1:

Drag an Execute Process Task from the SSIS Toolbox to the canvas.

Step2:

Double click the Execute Process Task, this will allow you to configure the tasks for your needs.

 

Step3:

Provide a name for your task.

Step4:

Configure task for server connection.

Field Value Explanations:

 

RequireFullFileName – True:

Determines whether the task fails if the executable is not found in thespecified path. Set to true if you would like to specify the folder path. Keep in mind, that the package will be running on another server when youdeploy the project to SQL Server.  If you know the path is accurate, youmay select true and define the path.  If you are unsure, you can selectfalse.  If the value is set to false and there are two files with the samename and different extensions, the task will run the first file it finds withthe defined name and does not take the file extension into consideration.Technically speaking, I could set this option to “False” and enter cmd.exe asthe executable. Process tasks  check the system 32 path or PATHenvironment for executables.

 

Executable –c:\windows\system32\cmd.exe:

The fully qualified path to the executable.

Arguments –"/c net use"\\server_name\folder_path" password /user:domain\user_name"

For Example: "/c net use "\\my_server\my_folder\ "PASSWORD1234 /user:my_domain\USER1234 "

Define the arguments for the executable. We are using the NET USE command toallow the SSIS server to connect to another server’s resources. You can findmore information on  NET USE here.

  • Replace server_name with the     server you wish to access.
  • Replace folder_path with the     path to the folder you wish to access.
  • Replace password with the     password for the user account you are using to access the server.
  • Replace domain with the     domain for the user account you are using.
  • Replace user_name with the     user account you are using, typically a service account.

WorkingDirectory –c:\windows\system32

Defines  the working directory of the executable. Enter the path wherecmd.exe is located. It is c:\windows\system32\ for this example.

 

FailTaskIfReturnCodeIsNotSuccessValue– True

If a failure code is received, fail the task.

Success Value – 0

Specify the value returned by the executable to indicate success. Bydefault, this value is set to zero. CMD.exe returns a value of zero ifsuccessful.

 

TimeOut - 0

Specify the number of seconds that the process can run. A value of 0indicates that no value is needed and will run until the process is completedor errors out. The connection does not require a timeout value.

 

WindowStyle -Hidden


Determines whether or not a window displays during processing.  Since wewill be executing the package on SSIS server, the value should be set toHidden.

Disconnecting from theResource

 You will want to make sure you disconnect from the server resourceafter using it.  This is to ensure the connection has been closed and thatthe server resource is available for other jobs that my need it.

Step1:

Drag an Execute Process Task from the SSIS Toolbox to the canvas.

Step2:

Double click the Execute Process Task, this will allow you to configure the tasks for your needs.

Step3:

Provide a name for your task.

Step 4:

Configure task for server disconnection.

Field Value Explanations:

 

RequireFullFileName – True:

Determines whether the task fails if the executable is not found in thespecified path. Set to true if you would like to specify the folder path. Keep in mind, that the package will be running on another server when youdeploy the project to SQL Server.  If you know the path is accurate, youmay select true and define the path.  If you are unsure, you can select false. If the value is set to false and there are two files with the same name anddifferent extensions, the task will run the first file it finds with thedefined name and does not take the file extension into consideration.Technically speaking, I could set this option to “False” and enter cmd.exe asthe executable. Process tasks  check the system 32 path or PATHenvironment for executables.

 

Executable –c:\windows\system32\cmd.exe:

The fully qualified path to the executable.

Arguments – "/c net use "\\server_name\folder_path" /delete /y"

For Example: "/c net use "\\my_server\my_folder " /delete/y"

Define the arguments for the executable. We are using the NET USE command toallow the SSIS server to connect to another server’s resources. You can find moreinformation on  NET USE here.

  • Replace server_name with the     server that has been connected to.
  • Replace folder_path with the     path of the connection.  
  • /delete/y – This command     deletes the connection without requiring confirmation.  If you omit     “/y”, the process will wait for user confirmation before deleting the     connection.

WorkingDirectory –c:\windows\system32

Defines  the working directory of the executable. Enter the path wherecmd.exe is located. It is c:\windows\system32\ for this example.

 

FailTaskIfReturnCodeIsNotSuccessValue– True

If a failure code is received, fail the task.

Success Value – 0

Specify the value returned by the executable to indicate success. Bydefault, this value is set to zero. CMD.exe returns a value of zero ifsuccessful.

 

TimeOut - 0

Specify the number of seconds that the process can run. A value of 0indicates that no value is needed and will run until the process is completedor errors out. The connection does not require a timeout value.

 

WindowStyle -Hidden


Determines whether or not a window displays during processing.  Since wewill be executing the package on SSIS server, the value should be set toHidden.

 

‍

← View all posts