1

I was wondering how do we ingest CSV files located on a Red Hat LINUX server into SQL Server Database Table.

I know we can write a Stored Procedure/Bulk Insert to read the files that are located on the same Windows Server as SQL Server and update the database but not sure how to do it when the files are present in LINUX.

Any help would be greatly appreciated

Improve this question

4 Answers 4

Reset to default
1

I think there are at least two generic options for this kind of thing:

  • have a network share that the SQL Server host can connect to on the Linux server.
  • sync the file from Linux server to the DB host (by e.g. using a network share on the DB host, or any other way of copying files over network).

Remark: The way Windows handles mapped network drives, it might be that the login session running the DB processes would not see the drive if it was mapped by some other user account. Thus the first option is preferred (maybe also from the pov of dbas?).

Improve this answer
5
  • Forgive me for being naive. I currently have a network share on the LINUX server but I'm not sure if DB host can connect to it. How do I check that.
    – user1345260
    Aug 5, 2013 at 10:53
  • Is the network share on Linux based on CIFS/SMB (or something else Windows can handle natively)? If yes, use the "Map network drive" option in Windows Explorer: Right click 'My Computer' -> 'Map Network Drive'. Enter the path (\\host\sharename, obviously substituting correct names for the Linux host and share name), and click 'Connect using a different username and password'. Now input the appropriate username and password.
    – zagrimsan
    Aug 5, 2013 at 15:37
  • Zagrisman - Thanks a lot for your reply. Here's the problem. I don't have RDP access to the SQL server. Is there a way I can test the connection without the RDP access? I know I'm being stupid and the answer will be NO but just wanted to confirm
    – user1345260
    Aug 5, 2013 at 15:43
  • As far as I remember, mapping drives needs to be done locally...
    – zagrimsan
    Aug 5, 2013 at 15:47
  • Since it is required that the login session running SQL Server sees the mapped drive, it is likely that mapping it by another user account is not going to work anyway (although I have not tried such a thing myself). I assume that the DB is managed by some admin(s), propably you could ask them for a suggestion to transferring data on the server. There might already be a shared folder for inbound files somewhere.
    – zagrimsan
    Aug 9, 2013 at 6:36
1

You can use SSIS to do this.

  1. Download PSCP (an SCP client, i.e. command-line secure file copy)
  2. Make sure to convert the files first on Linux server to windows format and then download to Windows using PSCP - to remove the ^M from the files since they are generated on Linux server.
  3. Now you can even use your SP or just use SSIS to bulk insert those into SQL Server.

Refer to : Transfer files from Linux to Windows or vice versa using SSIS

Improve this answer
1

You can skip the filecopy step and just insert the data directly to SQL Server. yum install freetds (use EPEL repository) and then use freebcp to insert the data into SQL Server from the linux box.

Improve this answer
1
  • FreeTDS also does have some limitations (such as issues with HA AlwaysOn setups) so read the documentation on it carefully.
    – LowlyDBA - John M
    Jun 25, 2015 at 16:15
1

Depeniding upon the servers connectivertiy, then SSH could be one of your best answers.

Using rscync, the you can pull the files or directories through from the red hat server or even push them to the Windows server.

NAME rsync - a fast, versatile, remote (and local) file-copying tool

SYNOPSIS Local: rsync [OPTION...] SRC... [DEST]

  Access via remote shell:
    Pull: rsync [OPTION...] [USER@]HOST:SRC... [DEST]
    Push: rsync [OPTION...] SRC... [USER@]HOST:DEST

  Access via rsync daemon:
    Pull: rsync [OPTION...] [USER@]HOST::SRC... [DEST]
          rsync [OPTION...] rsync://[USER@]HOST[:PORT]/SRC... [DEST]
    Push: rsync [OPTION...] SRC... [USER@]HOST::DEST
          rsync [OPTION...] SRC... rsync://[USER@]HOST[:PORT]/DEST

  Usages with just one SRC arg and no DEST arg will list the source files
  instead of copying.

Source: http://linux.die.net/man/1/rsync

so simple example:

dan@dan-ThinkPad-T410s:~/Pictures$ rsync -rv /home/dan/Downloads/ /home/dan/Pictures/

this copies all the information recusivly in my Downloads folder to my Pictures folder. This is the same principle when coping remotley.

This may allow you to move the files from one server to another.

if you wanted to open the channel from the server side, then you could use a program like putty, to open the the ssh chanel from the SQL server (official site of putty: http://www.putty.org/)

from here you can simply use your script to read and write the information to the database.

I hope this helps :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.