0

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?

Mister X
  • 3,406
  • 3
  • 31
  • 72
  • did you read this : [https://stackoverflow.com/questions/41489816/connect-sql-to-apache-nifi] – Prabhat G Jun 08 '17 at 10:46
  • @PrabhatG i have already tried that way.Those are form InsertQuery for every rows works good for smaller amount of data but i have huge amount of data that's why i choose bulk insert operation. – Mister X Jun 08 '17 at 10:54
  • bulk insert: do you mean calling `bcp` util ? https://learn.microsoft.com/en-us/sql/tools/bcp-utility – daggett Jun 08 '17 at 10:56
  • What error are you getting? Please post any an all Exception messages. – Rachel Ambler Jun 08 '17 at 10:57
  • Rachel i have posted my query and error faced.Can you give suggestion on this? – Mister X Jun 08 '17 at 11:14
  • dagget,i have used BulkInsert mentioned in question not bcp utility – Mister X Jun 08 '17 at 11:14
  • 1
    I have no idea what _nifi_ is, but I guess from your error message that `C:\Directory\input.csv` is not accessible from the SQL Server you are running it on. This path has to be valid _on the SQL Server_, not on whatever machine SSMS is running on – Nick.Mc Jun 08 '17 at 11:44

2 Answers2

3

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.
Andy
  • 13,916
  • 1
  • 36
  • 78
1

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'
SithApprentice
  • 43
  • 9