Solved

SSIS Component SharePoint 2016 Mapping on Number

  • 14 July 2023
  • 2 replies
  • 23 views
D
Badge

Hello,

 

I’m trying CData SSIS SharePoint Components for the first time.

 

I’m following the tutorial instructions to from CData to insert new or update existing records in SharePoint. 

Use the CData SSIS Components to Insert New or Update Existing SharePoint Records from SQL Server

 

Using SQL Server (2012) as a source, I am attempting to setup a CData SharePoint Lookup task, but cannot map an ID (int) column to the ID (number) column on SharePoint (2016). Apparently a number with 0 decimal places is a datatype DT_R8, so I cannot map these columns, as DT_R8 cannot be used to map due to not being exact.

 

I don’t see any particular option in SharePoint to make a number not a float.

 

It does seem a bit odd that there’s no way to map on a SharePoint number column, so maybe I’m missing something, or perhaps it’s because it’s SharePoint 2016.

 

Since I can map on text, I can modify my own SharePoint lists to use text columns, but it may not be so easy to get business partners to modify their lists to add an identical number column using a text data type.

 

Any ideas?

icon

Best answer by Ankit Singh 18 July 2023, 08:55

View original

2 replies

Ankit Singh
Userlevel 3
Badge +1

Hey @Daniel T 

Based on what you explained, it looks like you are trying to get data from SQL Server to SharePoint, utilizing a lookup component to find and update any existing records or insert new records.


The challenge you are facing with mapping an ID (Int) in SQL Server to an ID (number) in SharePoint is understood. 

SharePoint by default maps the numerical values as NUMBER: List and library column types and options - Microsoft Support

Under the column definition, can you try defining the Default Value: Calculated Value

 

Once you do this, you can change how these Calculated Values are read with CData by defining the values in the connection property here: https://cdn.cdata.com/help/RSH/rssis/RSBSharePoint_p_CalculatedDataType.htm.

Can you give this a try and see if it helps?

That Solutions Guy
D
Badge

Thanks for the response and sorry for the late reply, I went on vacation and then got caught up in other work… 

 

I tried changing the number column default value to calculated, but it would never stay as such and there was no option in the CData SSIS objects to change how they are read.

 

That’s a shame that something so basic as mapping a number to an int is either not possible or a convoluted mystery.

 

Thanks though.

Reply


Terms and ConditionsCookie settings