How to use MS-SQL BULK Import?

General
1

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:

image
image765×767 13.2 KB

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

image

Does anyone have an idea whats wrong here ?
BR

2

Try the bulk demo to see how it works.

image
image1329×764 83.8 KB

3

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

4

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.

5

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