Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Transfer files from Linux to Windows or vice versa using SSIS


By:   |   Read Comments (5)   |   Related Tips: 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

  1. In this sample I am going to copy a file from UNIX\Linux to Windows.
  2. I am using SSDT and SQL Server 2012, but you can use later or earlier versions.
  3. A txt file named test.txt in UNIX (in this Tip in the \ folder)
  4. 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 "myuser@173.20.0.16:/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.

  1. Open the SSDT or BIDS and create a new SSIS Project.

    Integration Service Project

  2. 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.

    Execute Process Task

  3. Double click on it and click the process Tab. Fill the Executable field, the WorkingDirectory and the Arguments.

    Execute task 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 "myuser@173.20.0.16:/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" "myuser@173.20.0.16:/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:



Last Update:






About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, November 14, 2014 - 8:50:43 AM - mpereira Back To Top

Thanks for this tip.  It's simple, elegant and effective!


Friday, January 24, 2014 - 1:17:54 AM - Veer Back To Top

Thank you for your reply. :)


Thursday, January 23, 2014 - 8:25:24 AM - Daniel Back To Top

You can use SAMBA, FTP, FTPS.

For more information about parameters, refer to this link:

http://the.earth.li/~sgtatham/putty/0.60/htmldoc/Chapter5.html


Thursday, January 23, 2014 - 2:15:16 AM - Veer Back To Top

Hi,

Is it posible to move a file fom linux to windows without installing any software like in this case you have used "PSCP".

Also, can you please let me know the more about the parametres you have provided in the the command line

c:\pscp\pscp -l myuser -pw mypwd "myuser@173.20.0.16:/stage/db/test.txt" "c:\destination"
 
Thanks,
Veer

Tuesday, January 07, 2014 - 11:51:22 AM - TimothyAWiseman Back To Top

As a user of both Linux and Windows, I rather like this tip.  It provides an elegant, simple approach and shows that SSIS can be extremely versatile partially because of its easy ability to integrate with other programs.

It is worth noting that there are many ways of accomplishing this though.  This tip mentions FTP.  Samba also provides a good approach for file sharing between unix and windows.  Its initial setup can be a little painful, but once you are past that it tends to make the day-to-day activities simple.  For small files on a personal basis, I use dropbox.  And there are yet more options beyond those.


Learn more about SQL Server tools