How to use MS-SQL BULK Import?

Hi all,

we are trying to use the mssql BULK import feature, but we are failing due to an error that we are not able to find.

This is the table we want to write our data to:
image

The first column, ValueFloat_ID is an auto-increment column, so it should not matter using the BULK Import feature.

We prepare an object with data in format that should fit the BULK function:


var e = msg.payload;
var obj = {};

var columns =  [    { name:'Sensor_ID', type:'nvarchar(500)' }, 
                    { name:'Sensor_Value', type:'float' }, 
                    { name:'Sensor_EpochTime', type:'datetime' }, 
                    { name:'Sensor_q', type:'bit' }  
                ]  ;


obj = [{'Sensor_ID': "IOT/260/DB_OPC.something.something.else", 'Sensor_Value': parseFloat(1), 'Sensor_EpochTime': "2023-04-12T10:09:56.530Z" , 'Sensor_q': true}];

node.send ({columns:columns, payload:obj});
        

And this is how the SQL BULK node is configured:

But all we get is this unfriendly error message :frowning: :frowning:

image

Does anyone have an idea whats wrong here ?
BR

Try the bulk demo to see how it works.

I do not want to use the column editor as in the example, I have to send the column names from a dynamic datasource

In short, you have not set all of the details required in your dynamic column specification.

If you 1st perform the operation using "fixed" columns & feed the result to a DEBUG node (set to show complete msg), you will see in the output results the EXACT format of columns required.

Try


const columns =  [
    {"output":false,"name":"Sensor_ID","type":"nvarchar(500)","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},
    {"output":false,"name":"Sensor_Value","type":"float","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},
    {"output":false,"name":"Sensor_EpochTime","type":"datetime","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},
    {"output":false,"name":"Sensor_q","type":"bit","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}
]

NOTE: Be sure to adjust the nullable and other properties to match your table & expectations

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.