Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from serverfault.com Aug 5 '13 at 9:43

This question came from our site for professional system and network administrators.

2 Answers

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?).

share|improve this answer
    
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 '13 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 '13 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 '13 at 15:43
    
As far as I remember, mapping drives needs to be done locally... –  zagrimsan Aug 5 '13 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 '13 at 6:36
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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