Sync Pages from External Database

J Anderson asked on January 31, 2018 19:47

Hello,

I am using Kentico 11 and a satellite MVC website. This is mostly just using Kentico for getting pages and page information. I also have a separate database of product content that is regularly updated by business members outside of Kentico. What I'd like to set up is a sync between that database and Kentico so that new products are automatically added as draft pages in Kentico, which triggers some workflow for the business to add website content and get it approved. This just needs to be done daily or weekly.

There would need to be some custom logic to ensure that:

Duplicate Items aren't created.
The items are created under the correct parent (via an ID that exists in both in Kentico and the database)

So, my question is... What's the best way to do this? I've Googled around for a solution and looked in this DevNet forum, but couldn't find much. Here are some of my theories - I'm just not sure where to start:

Use middle SQL database and the Integration Bus (Source: Import SKU via Integration Bus)
Create an external application that uses the Kentico API to get the data and insert the new items.
Use SSIS or a stored procedure to just move the data into the correct page type databases.
Use a scheduled task (Source: Using BulkInsert to Import Data)

pages

Correct Answer

Zach Perry answered on January 31, 2018 21:58

I do something similar, and I just use a scheduled task that runs nightly.

The task just calls a stored proc on another database, it finds any products that already exist and updates them with any new information. If any products don't exist, it creates the SKU, and the Page and assigns the sku to the page.

var skuToUpdate = SKUInfoProvider.GetSKUs()
                        .Where("SKUNumber", QueryOperator.Equals,  ValidationHelper.GetInteger(product["Item_Number"],0)).FirstOrDefault();

                    if (skuToUpdate != null)
                    {
                    //Update
                    }
                    else
                    {
                    //Create new sku
                    }

Any that are no longer coming over from the stored proc get disabled. I send an email at the end with any new Products that were created to have the additional information added. If you are using tax classes, make sure you add the products to the tax class when you create it.

This should give you most of the API calls needed to do everything:

1 votesVote for this answer Unmark Correct answer

Recent Answers

J Anderson answered on January 31, 2018 22:07

Thanks for your help, Zachary! That's kind of what I was thinking and seems doable.

0 votesVote for this answer Mark as a Correct answer