You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by prabhu Mahendran <pr...@gmail.com> on 2017/06/08 12:11:50 UTC
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?
Re: How to perform bulk insert into SQLServer from one machine to another?
Posted by Matt Burgess <ma...@gmail.com>.
You won't need/want NiFi for that part; instead you would need to
login to the machine running SQL Server, install an FTP daemon (such
as ftpd), then in the PutFTP processor in NiFi you can point to the
FTP server using the Hostname, Port, Username, Password, etc.
On Thu, Jun 8, 2017 at 12:18 PM, prabhu Mahendran
<pr...@gmail.com> wrote:
> Matt,
>
> Thanks for your wonderful response
>
> I think create FTP server is best way for me to move input file into sql and
> runs a query.
>
> Can you please suggest way
> to create FTP server in Sql installed machine using NIFI?
>
> Many thanks,
> Prabhu
>
> On 08-Jun-2017 6:27 PM, "Matt Burgess" <ma...@gmail.com> wrote:
>
> Prabhu,
>
> From [1], the data file "must specify a valid path from the server on
> which SQL Server is running. If data_file is a remote file, specify
> the Universal Naming Convention (UNC) name. A UNC name has the form
> \\Systemname\ShareName\Path\FileName. For example,
> \\SystemX\DiskZ\Sales\update.txt". Can you expose the CSV file via a
> network drive/location? If not, can you place the file on the SQL
> Server using NiFi? For example, if there were an FTP server running
> on the SQL Server instance, you could use the PutFTP processor, then
> PutSQL after that to issue your BULK INSERT statement.
>
> Regards,
> Matt
>
> [1]
> https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql
>
> On Thu, Jun 8, 2017 at 8:11 AM, prabhu Mahendran
> <pr...@gmail.com> wrote:
>> 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?
Re: How to perform bulk insert into SQLServer from one machine to another?
Posted by prabhu Mahendran <pr...@gmail.com>.
Matt,
Thanks for your wonderful response
I think create FTP server is best way for me to move input file into sql
and runs a query.
Can you please suggest way
to create FTP server in Sql installed machine using NIFI?
Many thanks,
Prabhu
On 08-Jun-2017 6:27 PM, "Matt Burgess" <ma...@gmail.com> wrote:
Prabhu,
From [1], the data file "must specify a valid path from the server on
which SQL Server is running. If data_file is a remote file, specify
the Universal Naming Convention (UNC) name. A UNC name has the form
\\Systemname\ShareName\Path\FileName. For example,
\\SystemX\DiskZ\Sales\update.txt". Can you expose the CSV file via a
network drive/location? If not, can you place the file on the SQL
Server using NiFi? For example, if there were an FTP server running
on the SQL Server instance, you could use the PutFTP processor, then
PutSQL after that to issue your BULK INSERT statement.
Regards,
Matt
[1] https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-
insert-transact-sql
On Thu, Jun 8, 2017 at 8:11 AM, prabhu Mahendran
<pr...@gmail.com> wrote:
> 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?
Re: How to perform bulk insert into SQLServer from one machine to another?
Posted by Matt Burgess <ma...@gmail.com>.
Prabhu,
From [1], the data file "must specify a valid path from the server on
which SQL Server is running. If data_file is a remote file, specify
the Universal Naming Convention (UNC) name. A UNC name has the form
\\Systemname\ShareName\Path\FileName. For example,
\\SystemX\DiskZ\Sales\update.txt". Can you expose the CSV file via a
network drive/location? If not, can you place the file on the SQL
Server using NiFi? For example, if there were an FTP server running
on the SQL Server instance, you could use the PutFTP processor, then
PutSQL after that to issue your BULK INSERT statement.
Regards,
Matt
[1] https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql
On Thu, Jun 8, 2017 at 8:11 AM, prabhu Mahendran
<pr...@gmail.com> wrote:
> 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?