AnsweredAssumed Answered

API TimeStamp

Question asked by cageybee on Aug 8, 2018
Latest reply on Aug 8, 2018 by tramachandran

Hi, I'm using an SSIS package to connect to PI System via the web API and to get the data for one of the plants. The plant is in LA and we are in St Louis. I am getting the data and it has a timestamp as DateTimeOffset and it gives the time back in terms of our time. How do I get the local system time instead of our time from the call? Here is my code. This is all happening inside of a data flow task  in a Script Component


#region Namespaces
using System;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Net;
using System.IO;

public class ScriptMain : UserComponent

    public class AFValue
        public string AttributePath { get; set; }
        [DataMember(Name = "TimeStamp")]
        private string TimeStampRaw { get; set; }
        public float? Value { get; set; }

        public DateTimeOffset? Timestamp
                DateTimeOffset result;
                if (!DateTimeOffset.TryParse(TimeStampRaw.Replace('T', ' '), out result))
                    return null;

                return result;

    public class AFValueSearchCriteria
        public string AttributePath { get; set; }
        public string StartDate { get; set; }
        public string EndDate { get; set; }

    public static List<AFValue> results = new List<AFValue>();

    public override void PreExecute()
        string equipmentStateIDPath = Variables.VEquipmentStateIDPath.ToString();
        string defaultStartDate = Variables.VWebServiceStartDate.ToString();
        string defaultEndDate = Variables.VWebServiceEndDate.ToString();
        string webAPIURL = Variables.PPWebAPIURL.ToString();

        AFValueSearchCriteria criteria = new AFValueSearchCriteria();
        criteria.AttributePath = equipmentStateIDPath;
        criteria.StartDate = defaultStartDate;
        criteria.EndDate = defaultEndDate;

        WebClient client = new WebClient();
        client.Encoding = Encoding.ASCII;
        client.Headers.Add("Content-Type", "application/json");
        client.Headers.Add("Accept", "application/json");

        MemoryStream mstream1 = new MemoryStream();
        DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(AFValueSearchCriteria));
        serializer.WriteObject(mstream1, criteria);

        mstream1.Position = 0;

        StreamReader reader = new StreamReader(mstream1);
        string jsonDataRequest = "[" + reader.ReadToEnd() + "]";

        string webAPIResponse = client.UploadString(webAPIURL, "POST", jsonDataRequest);

        DataContractJsonSerializer deserializer = new DataContractJsonSerializer(typeof(List<AFValue>));
        byte[] buffer = Encoding.ASCII.GetBytes(webAPIResponse);

        using (Stream mstream2 = new MemoryStream())
            mstream2.Write(buffer, 0, buffer.Length);

            mstream2.Position = 0;
            List<AFValue> deserializedResponse = deserializer.ReadObject(mstream2) as List<AFValue>;

            results = deserializedResponse;

    public override void PostExecute()

    public override void CreateNewOutputRows()
        foreach (AFValue item in results)
            ResultBuffer.AttributePath = item.AttributePath;
            ResultBuffer.TimeStamp = (DateTimeOffset)item.Timestamp;
            ResultBuffer.Value = item.Value.GetValueOrDefault();