I'm looking for some advice on how to best build SSIS packages using the PI-OLEDB provider.
We are looking to retrieve data from PI and write the results to a SQL Server database. The list of tags that we are interested in is maintained on a SQL server table.
I've built a package that works but there is certainly room to improve the performance and take advantage of any PI-OLEDB best practices that may not be obvious at first glance.
The basic structure of the package is:
- Retrieve list of PI Tags from SQL Server source
- For each row in the PI Tag name query, build a SQL query string to use vs. the PI system via OLEDB. Save the set of queries to an in-memory ADO recordset
- Use the For - Each loop control to process each SQL query string -> read results from PI system, write results back to SQL Server database
This package works as expected, but there is a lot of time burnt up in validation and cleanup steps. I'm concerned that if the "subscription list" continues to grow larger, we'll start compounding upon early design issues. Might as well address it now before things get too far along.
So, my question is: are there any general rule-of-thumb tips for using the PI-OLEDB provider within SSIS packages, any configuration / setting changes that are recommended, and so on?