Let’s continue. You can find the first part here . Now let’s focus on delta sync, the most difficult part here I think. These are logical steps we need to go through:
- Query Odata feeds with filter to bring updated and new items
- Delete records which exists locally
- Insert new and updated records.
We are not able to check every field of every new or updated item. It is too complex and will be really painful to maintain it after. So, for updated records, we just drop them and bring them again.
Drag and Drop new Data Flow task, call it “Delta Sync”. I am not going through the details here, just a quick overview:
If you look again at our logical steps, they will be reflected in SSIS actions here. First configure Odata task with filter and formating set to json:
And this is how we are deleting updated records, using entity id. In this example its DeliverableId.
The last step is just insert if all records, returned by Odata feed. Remember, we set filter.
Here is a list of entities which have Modified On date:
This is the final picture:
The only one step left. We need to pass Last Sync date as a parameter to all these Odata tasks. To do so, left click on Delta Sync task and select Expressions from Properties window:
Each Odata query parameter will be listed in Property filed. Expression will be the same for all fields except : Resources, BusinessDrivers, Prioritizations,
Also, for those of you who has their PWA instance located somewhere not in you Time Zone, we need to add one more variable to keep Local Time. Let’s say if your tenant located somewhere
in US, and you are in Australia, project Published Date will be -18 hours. So, we need to include this logic, otherwise updated records will come in 18 hours, what is not good, right?
Create a new Expression and add hours to align with Time Zone where your tenant is.
This is a result for Expression for Delta Sync:
Please, have a look at the code, expression for query is a bit tricky one, as we need to make right formatting for Odata filtering.
Now, you already have chance to test your SSIS package. Create a new Risk or Issue for any of projects and it should appear in DB.
The last part is to bring the most heavy feeds – Project Related entities.
Drag and drop new for-each loop on the main designer screen and create a new string variable “ProjectID”. This variable will keep projectID for each project record.
Drag and drop new Data Flow task inside for-each loop as shown below:
Edit For-each loop, we need to include ProjectID field together with ProjectPublsiedDate, so we can filter projects by updated/new.
Next we need to make an gate expression, to let process next activities only for new/updated projects:
Next, go inside Project Related Task Flow and follow the similar concept as we did for Delta Sync. The only difference, all Odata Tasks will have one parameter – ProjectID.
The whole picture will look like this:
These are all entities which will be updated, once project is published. One more simple step would be to pass ProjectID as a parameter to all those Odata tasks.
Hard to imagine, but we are done!
Now, you just need to parameterize all connections and deploy it.
Grab the source code and let me know how you go!
PS. During testing I have found that Odata task is a bit tricky. It requires you to have a valid ProjectID, as PreValidation step in executing SSIS will fail if you don’t provide real Projet ID. For such case, right click on the Task Flow and set Property “Delay Validation” to true:
P.P.S Also the third part is coming with updated SSIS package, as I missed one important step on how to bring Project Site data. Also, as I told before, it is a real project, and I have deployed that package to our dev environment for now for heavy testing. After 3 days of running I have come up with some good tips on how to improve the performance and make the package to be easily deployed between dev and prod. So stay tuned…