How to bulk load data files with text and/or binary values from a client machine into a db server without a file share?

Question

We have a C++ application that bulk loads data files (including text and binary values) from a client machine (either Windows or Linux) into an Oracle 10g database server that resides on its own box. We are in the process of converting the Oracle 10g database server to SQL Server (2008 or 2012) and are looking for ways to bulk load the same data files (including text and binary values) into SQL Server.

Since the client application executes on either Windows or Linux, we plan to use the Microsoft ODBC Driver to connect and execute queries/statements from the C++ application against the SQL Server that will reside on its own Windows server. We are NOT allowed to share directories on either the client or the db server; thus, we cannot BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) from the client since the data file must be shared between the two computers (see “Bulk Importing from a Remote Data File” at http://msdn.microsoft.com/en-us/library/ms175915.aspx). (Note: I am not sure whether we can overcome this issue by using “impersonation/delegation”).

Our data files are tab-delimited and we can’t change their current structure (i.e. bring binary value into data file).  Below is a sample database table and data file.

1) Example table:
CREATE TABLE [dbo].[CONTENT_TBL](
      [CONTENT_FILE_NAME] [varchar](256) NOT NULL,
      [CONTENT] [varbinary](max) NOT NULL,
      [WEEK_NUM] [decimal](2, 0) NOT NULL
) ON [PRIMARY]

2) Example tab-delimited data file with values for file name, directory path, and week number:
(Note: The second column contains the directory path to the binary file that has to be imported into column CONTENT_TBL.CONTENT).

zip1.txt<tab>C:\Data\zip1.zip<tab>02
zip2.txt<tab>C:\Data\zip2.zip<tab>02
image1.txt<tab>C:\Data\image.jpeg<tab>02

Any suggestions on how to proceed?

Thanks,
Perry.

---

Perry Likakis

Answer 1

Hi PerryLikakis,

Regarding to your description, seems you tired to bulk insert into SQL Server from remote computer.

When executing the BULK INSERT statement from one computer, inserting data into SQL Server on a second computer.

When you want the server to use a remote server for the .txt file location in the bulk insert If the .txt file is on a different machine, UNC path might work to allow the sqlservr.exe get to the text file across the share. However, it would be a challenge to get the bulk insert code to get permissions to a UNC file share.

When you specify the location of the text source file, consider the following:
 •The server must have permission to access both the file and the destination database.
 •The server runs the Bulk Insert task. Therefore, any format file that the task uses must be located on the server.
 •The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.
Meanwhile please use SQL Server Authentication and specify a SQL Server login, which uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation.

>> plan to use the Microsoft ODBC Driver to connect
To use remote connections, please try to change to an OleDb destination instead.

For issue about UNC file share, please link to the SQL and Directory Service support teams could help.

 

---

Regards, Amber zhang