Put MongoDB Data into SQL Server

[#1]

Hello All,

I am completely new to nifi. But as i found it is very interesting to learn.

I am trying to migrate the MongoDB Data into sql server using NIFI.

Is it possible.

If yes, please suggest me what are the steps that i need to follow.

Thanks,

Mohan V

[#2]

@Mohan V

Yes, it's possible.

Use GetMongo processor to read the data from the mongo collection and Use PutDatabaseRecord processor to store the data into SQL server.

In PutDatabaseRecord processor Configure the RecordReader controller service to read the incoming Mongo data then the processor will store the data into SQL server.

Refer to this and this links for usage/Configuring of PutDatabaseRecord processor.

Flow:

1.GetMongo 
2.PutDatabaseRecord

-

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

Wow..That was quick and clear..

Thanks for the reply @Shu.

Will get back to you once i try this.

[#4]

Thanks for the reply @Shu.

I tried what you have suggested but i got struck.

I ll give you the screen shots of what i have done..

Please correct me where i was doing mistake.

GetMongo Processor:-

PutDataBaseRecord Processor:-

I have mentioend

Record Reader -- avroreader

statement type - INSERT

Database connection pooling service - DBCPCONNECTIONPOOL

schema name - dbo

Table Name - Locations

Rest is as they are

then

AvroReader:-

AvroShemaRegistry:-

DBCPConnectionPool:-

And then i created upstream connections and started.

But i am authentication error which is something weird for me as the same user id and credentials i am using in other applications to get the data from sql also.

Error as

Can not create pooling connection factory (login failed for user pbiuser).

Can you please help me with this.

I have tried for every possibility with that userid and password.but it didnt worked.

My sql server, and nifi server both are in same machine.

[#5]

@Mohan V

NiFi not able to create connection pool as it is failing to login with the username/password.

Please check is there any firewalll blocking and make sure all the connection details and db drivers are configured correctly.

[#6]

@Shu Im still having the same issue.

I checked the firewalls and they are off.

And i have downloded the jdbc drivers for sql and put it onto C:\Program Files\Java\jre1.8.0_171\lib\ext this directory

In PutDatabaserecord:-

Url:- jdbc:sqlserver://hjcorpsql-04:1433;databaseName=Test1;user=MyorganizationName\pbiuser;password=Secure@99;

ClassName:- com.microsoft.sqlserver.jdbc.SQLServerDriver

Driver Location:- E:/Software/sqljdbc_6.0/enu/jre8/sqljdbc42.jar

untitled5.png

Im running my nifi onto production system.

And the sql server is SQL Server Enterprise Edition 2016 and Windows Server 2012 R2.

Please help me.

I have been struck here from last one week.