We have one site in which external content types are set up and data is stored in SQL Server On premise. Now we want that site to be migrated to SPO. What is the best way to have this set up in SPO? Is it moving to Azure SQL, setting up CSV in shared folder or any other approach which will be less costly and less complex too?
2
-
How data is flowing in current / on prem environment ? Is Data is flowing from SP to SQL ?– Rajat SahaniCommented Apr 6, 2022 at 11:41
-
@RajatSahani Yes data is flowing from SharePoint to SQL and some columns are also showing data from SQL which is mapped to columns in SharePoint– AdarshCommented Apr 7, 2022 at 12:32
Add a comment
|
2 Answers
Azure SQL will be good choice.
Azure SQL <----> Web API <------> SharePoint Online (SPO)
answered Apr 6, 2022 at 11:48
-
Is there any web article which i can reference for setting up this Web API and Azure SQL migration?– AdarshCommented Apr 7, 2022 at 12:33
-
EF core will be best approach. Refer this article. docs.microsoft.com/en-us/aspnet/core/tutorials/… Commented Apr 7, 2022 at 14:55
This approach will have an issue with relational fields (if any) and column types (is it safe to assume all column types are string?). A simpler approach would be to create a view that resolves all the relational fields and then write that view out to a csv file, then read the csv file using powershell and write each row out as an item (see below). You can also, setup the site columns before hand to be of the appropriate type.
Code assumes you have SharePoint PnP installed and that the view is tab delimited
# read the records in that you want to write out to the Destination Site
$rows = Import-CSV -Path $data_to_migrate -Delimiter `t
# connect to the destination site
Connect-PnPOnline -Url $DestinationSite -Interactive
Foreach ($row in $rows) {
Add-PnPListItem -List $DestinationList -Values $row
}
