topic tMSSQLOutputBulkExec_1_Cannot bulk load in Talend Studio

tMSSQLOutputBulkExec_1_Cannot bulk load

Darmesh — Fri, 15 Nov 2024 23:59:30 GMT

I am loading data from source table directly to target table using tMssqlOutputBulkExec.

When i check the file mssql_data.txt it has all the data in it. But data is not loading into table

It is throwing me below error.

Exception in component tDBOutputBulkExec_1_tMBE (a)

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load because the file "C:/Users/DSB1129/Videos/mssql_data.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743)

at project.a_0_1.a.tHiveInput_2Process(a.java:8306)

at project.a_0_1.a.tHiveConnection_5Process(a.java:639)

at project.a_0_1.a.runJobInTOS(a.java:8909)

at project.a_0_1.a.main(a.java:8679)

[FATAL] 17:16:40 project.a_0_1.a- tDBOutputBulkExec_1_tMBE Cannot bulk load because the file "C:/Users/DSB1129/Videos/mssql_data.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-6.0.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743) ~[mssql-jdbc-6.0.0.jar:?]

at project.a_0_1.a.tHiveInput_2Process(a.java:8306) [classes/:?]

at project.a_0_1.a.tHiveConnection_5Process(a.java:639) [classes/:?]

at project.a_0_1.a.runJobInTOS(a.java:8909) [classes/:?]

at project.a_0_1.a.main(a.java:8679) [classes/:?]

[statistics] disconnected

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

gjeremy1617088143 — Thu, 01 Jul 2021 12:00:19 GMT

I, as the error message say : The system cannot find the path specified for the file path C:/Users/DSB1129/Videos/mssql_data.txt"

are you sure you have set the right path ?

Send me Love and Kudos

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

Darmesh — Thu, 01 Jul 2021 12:15:54 GMT

yes the path is correct and i can see the data in .txt file.

Even i tried using tMssqlOutputBulk and tMssqlBulkExec still the same error

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

gjeremy1617088143 — Thu, 01 Jul 2021 12:26:59 GMT

could you send a screen of basic settings and advanced settings of the toutputBulkexec ?

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

Anonymous — Thu, 01 Jul 2021 13:47:13 GMT

Hi @Darmesh Sureshbabu,

You have added a Talend Cloud label. Are you running this job in the cloud, on a Remote Engine or in the Studio? I presume that this file is on the same machine as the job is being run?

Regards

Richard

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

Darmesh — Thu, 01 Jul 2021 15:16:40 GMT

yes it Cloud. But now testing in studio It is throwing error.

Yes file is in the same location where the job is being run.

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

Darmesh — Thu, 01 Jul 2021 16:05:07 GMT

@Richard Hall

Can you tell me how this can resolved. or there might be issue with SQL Server access for BULK mode?

Since it is a straight forward one, am loading data src: Hive table to tgt: MSSQL

Help me out to fix this

Re: tMSSQLOutputBulkExec_1_Cannot bulk load

Anonymous — Thu, 01 Jul 2021 16:16:20 GMT

@Darmesh Sureshbabu sorry, I misunderstood the original error. This is caused because the file you are using needs to be accessible by the database that you are loading to. You are actually getting the db to process the physical file. The error here is from the MSSql driver....

The documentation for this component does briefly mention this here....

https://help.talend.com/r/lZLCVAvlC2eua7~d4oeP4w/jx7WfA9AmZfF1DQZI14OZA

It's an easy one to miss though. Can you relocate the file to be on the same machine as the DB and then ensure the user that is running the DB has access to the location where the file is?