1 of 1 people found this helpful
To be clear -- you're trying to join data coming out of SSIS onto PI AF attributes, and then store the data in PI tags underlying those attributes?
As you've probably seen, our SQL connectivity products which expose PI AF are read-only. However, with a bit of SQL magic, it's possible to use PI OLEDB Enterprise or the equivalent part of the new PI ODBC to query for AF Elements, Attributes, and their corresponding PI Tags. From there - you can use those mappings in PI OLEDB Provider (Legacy) which talks to the PI Archive and is read/write. Not pretty or straightforward, but it does work.
In your case, since you're in SSIS and thus have some nice flexibility, you might be able use the PI Web API for all of the above, with a little bit of scripting.
Or, you could do the integration from the PI side - using the PI RDBMS interface. At its core, that interface is a bit old-school, but AF can build PI tags in bulk, following a tag creation template you put together. So in this case, the PI tags historizing the data would tell the PI RDBMS interface what data to go and extract from SQL. No custom integration required - only configuration of an interface and tags.
1 of 1 people found this helpful
Echoing Brandon on the first part - are you trying to store historical data in PI, or just view current values in AF?
Apologies in advance if this answer is more basic than you're looking for or if Brandon Perry already got you what you needed.
On our system, we have a lot of PI Attributes in our AF structure that come from linked tables, but most of that is just context data. For process data we pretty much use the RDMBS interface universally (and then have AF attributes mapped to these PI Points). You can use AF to create the PI Points, or also, depending on how your data is structured, RDBMS can dynamically create the points for you through the interface.
I'm definitely not a SQL expert, but SSIS allows you multiple output options, OLEDB, Flat File, and Recordsets being the most interesting for PI. You could bring in the flat file via UFL or you can set up an RDBMS interface as mentioned above to bring in data from a record. None of this really requires scripting.
One other thing that might be worth mentioning is that I've been using PI Analysis Services quite a bit. While not as powerful as SSIS (especially if you've already got some algorithms running there) PI Analytics does have some potential from Data Transformations and makes it easy to put that data back into other PI Tags.
Hi Brandon and Brandon,
I'm not concerned about pushing data through to PI points, as I can do this by other means (I am using RDBMSPI for loading data to tags, which is great - and I use AF element templates to auto-configure tags when creating a new asset). What I want to do is store slowly varying fields in the version history of the (configuration item) attributes belonging to AF elements, where each AF element is an asset and the attribute is a property of that asset which doesn't change very frequently.
I suspected that PI Web API might be an option, but I haven't set that up for my system yet and was a bit wary of introducing that extra complexity into the system at this stage (particularly as PI Web API appears to be a fairly new offering).
I have written C# and VB code in the past, to push data from a dedicated application into configuration item attributes of the AF using function calls on the AF SDK. This works fine and would be a good option here too, but I was checking to see whether there was another solution (less dependent on coding) to what I would think was a fairly common need.
Thanks very much for both your inputs - you have been really helpful!
Would us of the PI Batch interface do what you need? You can generate CSV files with your PI point data and just drop the files into the PI batch folder for processing...
you would, of course, need the PI batch interface....