CODEWITHSUNDEEP
sqlsql-serversql-server-2008bulkinsertapache-nifi
Mahendran V M
Mahendran V M

Reputation: 3496

How to perform bulk insert into SQLServer from one machine to another?

i have running nifi instance in one machine and have SQL Server in another machine.

Here i can try to perform bulk insert operation with bulk insert Query in SQLserver. but i cannot able insert data from one machine and move it into SQL Server in another machine.

If i run nifi and SQL Server in same machine then i can able to perform bulk insert operation easily.

i have configured GetFile->ReplaceText(BulkInsertQuery)-->PutSQL processors.

I have tried both nifi and sql server in single machine then bulk insert works but not works when both instances in different machines.

I need to get all data's from one machine and write a query to move that data into SQL runs in another machine.

Below query works when nifi and sql server in same machine

BULK INSERT BI FROM 'C:\Directory\input.csv' WITH (FIRSTROW = 1, ROWTERMINATOR = '\n', FIELDTERMINATOR = ',', ROWS_PER_BATCH = 10000)

if i run that query in another machine then it says..,"FileNotFoundError" due to "input.csv" in Host1 machine but runs query in sql server machine (host2)

Can anyone give me suggestion to do this?

Upvotes: 0

Views: 1284

Answers (2)

Andy
Andy

Reputation: 14184

The SQL query is being executed on the machine that hosts the SQL Server application. Because the query defines the incoming data with a file system path, the machine that attempts to resolve that path is the SQL machine. The data does not exist at that path, and thus, cannot be loaded. You have a couple options to handle this:

  1. Use NiFi to move the data to a location on the SQL Server instance to be loaded during the SQL query execution. You can use GetFile/PutFTP, or ExecuteStreamCommand with RoboCopy (a Windows analog to rsync) -- this will avoid the cost of bringing the content into NiFi at all.
  2. Use NiFi to ingest the data from the local system into the content repository and then craft a SQL insert statement that reads the actual data rather than providing a file system path.

Upvotes: 3

SithApprentice
SithApprentice

Reputation: 53

Since I cannot comment, and this may be stupid to ask, but when you run on two separate machines, could you not have a batch job preform move to a common network location? Or FTP the needed data to a location on your SQL machine? Since I do not know what Nifi is, I'm not sure, but making sure nifi moves the data to a common location accessible by both your SQL and NIFI machines is the first thing I would do. Then just run your bulk insert while point to this location.

BULK INSERT BI FROM 'Some network directory' WITH 'you with clauses'

Upvotes: 1

Related Questions