I was thinking to keep only two parts, but seems that I missed couple impotent things. I mentioned before, that I am working on a real project now, and what you see here, is what we are going to use for quit a large company. The solution, which we have created in two previous posts works really well. From now, I already have couple good suggestions to share and couple things which I missed.
So, first, I would like to go through couple good suggestion, which will save you time and increase the performance of your solution. By the way, for medium-large size project, it takes about 3 minutes to bring all related data. I think it could be even better if you have good connection. But remember, it happens only first time. Next time when the SSIS package runs, it will bring only deltas. But there are still some entities which will be removed and recreated. Anyway, if you are gong to run the package 3-4 times per day, you will be fine.
Now, the important part which was missed from the previous posts is “Bringing data from Project Sites”. And its not only related to your custom Lists. Let’s say you add a new column to Risk register. You think it will be available through Odata feed for Risks? No, it doesn’t. You will need to find another way or just use SSIS package to bring that data.
Bringing data from Project Sites
Until now we were covered with Odata task. We have just one url and all we need to do is just to change query dynamically to bring data for specific project. With Project Site’s data, the situation is a little bit different. URL parameter is not available for change in Odata task. I have found somewhere the good post where the connection manager is changed in a script task before Odata task is executed. This solution is good, however I had some problems with validations and it just didn’t work the way I wanted.
So first let’s add a new package to our solution. We have to separate them as logically two packages are not really related, except the scenario when project is removed. The logic steps will be:
- Get list of all projects and their Site urls (will be part of the first package)
- Find if there any deleted projects, which still have items in local tables. Remove items from lists. (stored procedure in the first package)
- Get new/updated items. Add extra column to keep Project ID.
It is easy for us to include the first and the second steps into the first package, as we already have a stored procedure to deleted project related records there, which takes Project ID as parameter. Just add all your custom lists to that stored procedure.
Another important thing to mention, is that as we could have many different EPTs, project sites could have different structure and different lists. So we need to be ready to handle it in our SSIS package.
To start, let’s create ta new custom list in Project Sites:
First, on the picture below, the Stakeholder list. I used site existing site columns and Metadata types. We need to test, that solution will work not only for simple, but also for complex types.
Once we have created a list, we need to add one item just for testing. As we are going to keep all stakeholders just in one SQL table, we need to create an extra column to keep the project id, so after we know to which site the item belongs to.
Copy and past all connection managers from the first package:
Also, copy and past first 4 tasks from the first project as well. I have changed a little bit the SSIS package to include all pre configuration steps, like creating a DB if its not exists and all other tables. It will help you with deployment later.
Now we need to create one more connection manager, which we will be using to access all project’s sites.
This is just a OData connection manager, similare to the one we have created before. However, this connection is pointed to one of the project site to \_vti_bin\listdata.svc. We will be changing the URL fater, but for now we need it to be pointed to the existing project to avoidd validation issues.
Now, we need to change “Build Schema” expression to include the definition for our Stakeholders table:
Once this step is done, we are ready for the main part. First we will get all project’s ids and corresponfing Site URLs. To do so, drag and drop a new task flow, or you can copy and past the task from the first package – “Get All Projects”.
Dont forget to remove the section for deleted projects. This is how it shoud look inside:
Projectid and ProjectWorkspaceInternalUrl are two the main fields here. We also need to validate that SiteURL is not null. So, create two variables to keep them.
Drag and drop new foreach loop task and edit mapping to set those two variables:
Now drag and drop new SQL task inside foreach loop. We need to delete first all Stakeholders records by Project ID and after bring them again.
Next, create a new Data Flow task. This task wil keep the logic for updating Stakeholders. We are not going to use Odata task. as this task component doesnt provide good support for metadata fields and user fields. I have updated the existing SSIS SharePoint List data provider, you an find it here: http://ioi.solutions/ssis-sharepoint-list-adapter-sharepoint-online/ . It has really good support for complex fields and also I have added support for SharePoint online.
The only one extra thing needs to be done: we need to bring Project ID field for each stakeholder record. As we are going to store all Stakeholders record in a one table, we need to differentiate them by Project ID.
This is how the task looks inside:
SSIS SharePoint List data adapter supports parametrized Site URL, so we just can pass our Project Site variable straight to the task.
And we are done with the main part. However, as we are going to have different site templates for different projects, then lists could be different for each site. If you run the package now, it will fail, as your project sites probably dont have Stakeholder lists. So we need to handle such validation errors:
First thing -set DelayValidation for each main data flow task inside your foreach loop to true.
The second thing is to set MaximumErrorCount propety for Foreach loop to 0. It helps us to keep loop moving even if SharePoint List data adapter can’t find the list. So it will prevent the whole package from failure.
This is it. I have uploaded the whole solution below again, so you can have a better understanding.