By: Daniel Calbimonte | Updated: 2014-01-06 | Comments (5) | Related: More > Integration Services Development
Problem
I have a need to transfer files from Linux/Unix based system to my Windows server and then use SSIS to load the data into SQL Server. What are the steps to pull the files from the other operating system using SSIS?
Solution
The most common approach would be to use the FTP task, but others use third party tools rather than FTP for security reasons (ftp is like honey for hackers). So, in this tip we are going to use a nice third party tool that you may also like for its simplicity, performance and security. The advantage of this tool is that it is specialized for copying files. If you have a little DOS knowledge, it is pretty easy to understand.
Requirements
- In this sample I am going to copy a file from UNIX\Linux to Windows.
- I am using SSDT and SQL Server 2012, but you can use later or earlier versions.
- A txt file named test.txt in UNIX (in this Tip in the \ folder)
- For this tip we will use a free tool named pscp. You can download the tool here:
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
Copy the pscp.exe file from the link to a folder. In this tip, the pscp.exe will be copied to the c:\pscpfolder path
Using PSCP to copy a file
In this example we are going to copy a file from a SOLARIS 11 Server to the c:\destination path:
c:\pscp\pscp -l myuser -pw mypwd "[email protected]:/stage/db/test.txt" "c:\destination"
Make sure to assign privileges in the source and destination in order to copy files. I have setup Linux user myuser with password mypwd.
The command runs the pscp.exe file which is in the pscp folder and we are connecting with the SOLARIS user myuser with the password mypwd. We are connecting to the SOLARIS IP 173.20.0.16 and copying the text.txt file from the /stage/db path to the Windows machine in the c:\destination.
The UNIX/Linux permissions are out of the scope of this TIP, but you can refer to this link for more information: http://en.wikipedia.org/wiki/Chmod
Putting it all together with SSIS
Now we are going to work with SSIS.
- Open the SSDT or BIDS and create a new SSIS Project.
- Drag and drop the Execute Process Task to the design pane. This task executes any process file. In this tip we will execute the pscp.exe file.
- Double click on it and click the process Tab. Fill the Executable field, the
WorkingDirectory and the Arguments.
- The Executable file is the name of the .exe file. In this case the pscp.exe.
- The WorkingDirectory is the path where the pscp.exe is located.
- Finally the Arguments are the parameters used. In this case we are passing parameters to copy files from UNIX to Windows: -l myuser -pw mypwd "[email protected]:/stage/ db/test.txt" "c:\destination". If you do not understand the parameters review the first part of this tip above.
How can I copy a file from Windows to Linux\UNIX?
It is similar from Linux\UNIX to Windows, but the arguments would be: -l myuser -pw mypwd "c:\destination\test.txt" "[email protected]:/stage/ db/".. In this example, we are copying the test.txt file from Windows to UNIX using the user myuser.
How can I copy files including the directories?
Pscp by default only copies files, but you can use the -r option that lets you copy directories and contents as well.
Next Steps
For more information, refer to these links:
- http://the.earth.li/~sgtatham/putty/0.60/htmldoc/Chapter5.html
- http://technet.microsoft.com/en-us/library/ms141166.aspx
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-01-06
