WinCC Data Archiving to SQL: Configure OLEDB & User Archives

Overview: WinCC Production Data Archiving to SQL

When a WinCC SCADA project spans four or more PLCs and a finished part must be tracked as it leaves the last machine, the engineer faces a recurring question: where does the product ID and exit timestamp go so that IT and the Control Process Department can read it without opening WinCC? The Siemens SIMATIC WinCC V7.5 SP2 System Manual treats this as a layered problem: high-speed process values go to Tag Logging, free-form product metadata and string IDs go to either the optional User Archives or a custom Microsoft SQL Server table, and externally accessible reporting is exposed through the WinCC OLEDB Provider or OPC HDA / OPC A&E.

This reference consolidates the five engineering approaches that are commonly used in TIA Portal and classic WinCC projects, the licensing implications for each, and the SQL Server configuration required to hand the resulting tables to a corporate IT department without exposing the SCADA runtime to direct database manipulation.

WinCC Data Storage Architecture

Every WinCC station contains three SQL Server databases by default after the project is activated:

WinCC V7.4 and later ship with Microsoft SQL Server 2014 (WinCC V7.4/V7.5) and SQL Server 2019 (WinCC V7.5 SP2 Update 9 and later). The internal database engine is the same across TIA Portal WinCC Professional and classic WinCC; only the licensing model and the configuration dialog differ. Microsoft documents the supported SQL Server versions per WinCC release in SIMATIC WinCC V7.5 - System Manual, Section 3.3.

Archive Segment Lifecycle

Tag Logging segments cycle on size, time, or a defined trigger event. The cycle default of 1 MB / 1 day / single tag results in a per-day database that is renamed to CC_<Project>_T_<YYYYMMDD>_<HHMMSS>_Archive.mdf at the configured rollover. Alarm Logging uses a fixed 60-day buffer inside the live _A database plus optional file-based segments when archive server is enabled.

Method Comparison: Five Approaches for Product ID Storage

Method 1: WinCC User Archives (Recommended for Product ID + Timestamp)

User Archives is the Siemens-blessed mechanism for storing free-form string data such as product IDs, lot numbers, and operator actions. The WinCC V7.5 User Archives manual defines the field types, indexes, and trigger semantics that apply in this scenario.

Field Definition for a Product Tracking Archive

Triggering a Record from PLC Process Value

User Archives accept one-shot inserts through the UA scripting interface. Wire the four PLCs to a single WinCC station through the standard AS-OS connection; the project uses one combined User Archive, not one per PLC.

' VBScript in WinCC User Archive control
Dim uaConn, rs
Set uaConn = CreateObject("UserArchive.Ctrl")
uaConn.Connect "UA_ProductionLog"
Set rs = uaConn.CreateRecordset()

rs.Fields("ProductID").Value = HMIRuntime.Tags("UA_ProductID").Read
rs.Fields("MachineID").Value = HMIRuntime.Tags("UA_MachineNo").Read
rs.Fields("ExitTime").Value   = HMIRuntime.Tags("UA_TimeStamp").Read
rs.Fields("Operator").Value   = HMIRuntime.Tags("SysSet_UserName").Read

rs.Update
ovaConn.Disconnect

For high-throughput machines, change Update to UpdateBatch and accumulate 50-100 records before commit. This reduces the per-record transaction overhead against the SQL Server log.

IT Read Path to User Archives

The IT department can query the archive table directly from any SQL Management Studio instance, or through Power BI, Excel Power Query, or SSRS. The User Archives data is stored in a single UA#<ArchiveName> table inside the runtime database CC_<Project>_R. The structure is intentionally flat: there is no normalization, so reading is trivial. The Microsoft SQL Server Extended Events trace can be used during commissioning to confirm the IT user is reading without locking the runtime insert path.

Method 2: Custom SQL Table via VBScript / ADO

For projects that require fields outside the User Archives limit (e.g., a 1024-character comment or a BLOB containing a vision-system defect image) the engineer must create a custom table and write to it through ADO. The WinCC V7.5 VBS Reference documents the COM objects that are safe to call from runtime; ADODB is one of them.

SQL Table Definition

CREATE TABLE dbo.ProductionLog
(
  LogID       INT IDENTITY(1,1) PRIMARY KEY,
  ProductID   NVARCHAR(64)  NOT NULL,
  MachineID   TINYINT       NOT NULL,
  ExitTime    DATETIME2(3)  NOT NULL,
  CycleTimeMs INT           NULL,
  DefectImage VARBINARY(MAX) NULL,
  CONSTRAINT UX_ProductID_MachineID UNIQUE (ProductID, MachineID, ExitTime)
);

CREATE INDEX IX_ExitTime ON dbo.ProductionLog (ExitTime);

Write Path from VBScript

Dim conn, cmd
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = _
  "Provider=SQLOLEDB;" & _
  "Data Source=(local)\WinCC;" & _
  "Initial Catalog=CC_Production_R;" & _
  "Integrated Security=SSPI;"
conn.Open

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1  ' adCmdText
cmd.CommandText = _
  "INSERT INTO dbo.ProductionLog " & _
  "(ProductID, MachineID, ExitTime, CycleTimeMs) " & _
  "VALUES (?, ?, ?, ?)"

cmd.Parameters.Append cmd.CreateParameter("@PID", 202, 1, 64, HMIRuntime.Tags("UA_ProductID").Read)
cmd.Parameters.Append cmd.CreateParameter("@MID", 17,  1, 4,  HMIRuntime.Tags("UA_MachineNo").Read)
cmd.Parameters.Append cmd.CreateParameter("@ET",  135, 1, 8,  HMIRuntime.Tags("UA_TimeStamp").Read)
cmd.Parameters.Append cmd.CreateParameter("@CT",  3,  1, 4,  HMIRuntime.Tags("UA_CycleTime").Read)
cmd.Execute

conn.Close

Method 3: WinCC OLEDB Provider for External Read Access

The WinCC OLEDB Provider is the documented interface for IT-side reading. It exposes Tag Logging, Alarm Logging, and User Archives through a uniform COM/SQL API. The WinCC V7.5 OLE DB Provider manual lists the three connection string formats and the supported query syntax.

Connection Strings

Read Query Example (C#, IT Side)

using (OleDbConnection conn = new OleDbConnection(
  "Provider=WinCCOLEDBProvider.1;Catalog=CC_Project_R;Data Source=.\\WinCC"))
{
    conn.Open();
    using (OleDbCommand cmd = new OleDbCommand(
      "SELECT ProductID, MachineID, ExitTime, Operator " +
      "FROM UA_ProductionLog " +
      "WHERE ExitTime >= '2025-01-01' AND ExitTime < '2025-02-01' " +
      "ORDER BY ExitTime DESC", conn))
    using (OleDbDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            Console.WriteLine(
              $"{dr[\"ProductID\"]} | M{dr[\"MachineID\"]} | " +
              $"{dr[\"ExitTime\"]:yyyy-MM-dd HH:mm:ss.fff}");
        }
    }
}

The WinCC OLEDB Provider is read-only by design. It can never insert, update, or delete; this isolation protects the runtime database from the IT side. For two-way data exchange, the IT department must read from a replicated copy, not the live database. Microsoft's Data Archiving Strategies for SQL Server article describes a tiered approach where live tables feed a reporting database that IT can query freely.

Method 4: Alarm Logging Text Channel (Free, No Add-on)

When the project budget cannot absorb a User Archives license and the volume is below 200 events per hour, the alarm text channel can carry the product ID. The WinCC V7.5 Alarm Logging manual documents the 255-character limit on the user text block, the way @...%s... format placeholders pull tag values, and the resulting SQL storage in dbo.MSG / dbo.AlgCS.

Message Configuration

Message Number:    1000001
Class:            Production
Type:             Information
Text:             "Part %s left machine %d with cycle %f s"
Tag 1 (%s):       UA_ProductID    'String' (length 64)
Tag 2 (%d):       UA_MachineNo    'Direct'
Tag 3 (%f):       UA_CycleTime    'Direct'

Trigger the message with SetTagDWordWait from VBS on the global MSG_Production_Trigger tag. The result is a permanent row in CC_<Project>_A that IT can read with a basic SQL query against dbo.MSG joined to dbo.MSGVB for the parameter values. This method is the lowest cost, the slowest query, and the most sensitive to archive segment rollover.

Method 5: OPC HDA and OPC A&E Client

OPC Historical Data Access (HDA) reads numeric trends from Tag Logging; OPC Alarms and Events (A&E) reads Alarm Logging including the text channel. The two protocols together cover every data source inside WinCC. The OPC Foundation defines the standard; the Siemens WinCC OLEDB/OPC manual documents the WinCC implementation.

OPC HDA cannot return strings. OPC A&E returns the alarm source and message text, which is the same data as Method 4 but pulled through a different transport. OPC is appropriate when the IT-side historian is a third-party product (PI, Ignition, Wonderware) that already has OPC clients.

Licensing Requirements by Method

SQL Server Configuration for IT Hand-off

Two prerequisites must be met before the IT department can read WinCC data with a standard SQL client.

1. Create a Read-Only Login

USE [master];
CREATE LOGIN [DOMAIN\WinCC_ReportReader] FROM WINDOWS;

USE [CC_Production_R];
CREATE USER [DOMAIN\WinCC_ReportReader] FOR LOGIN [DOMAIN\WinCC_ReportReader];
GRANT SELECT TO [DOMAIN\WinCC_ReportReader];

-- Optional: grant SELECT on specific tables only
GRANT SELECT ON dbo.UA_ProductionLog TO [DOMAIN\WinCC_ReportReader];
DENY  SELECT ON dbo.AlgCS            TO [DOMAIN\WinCC_ReportReader];

2. Enable the TCP/IP Listener

SQL Server Express installs with TCP/IP disabled. Open SQL Server Configuration Manager, expand SQL Server Network Configuration, enable TCP/IP, restart the WinCC SQL service, and confirm the static port (default 1433) is reachable from the IT subnet. Microsoft's Configure a Server to Listen on a Specific TCP Port article documents the procedure.

3. Cold-Storage and Retention

WinCC segments are not auto-archived to .bak files. Schedule a SQL Server Agent job that runs daily, executes a BACKUP DATABASE for each archive segment older than 14 days, copies the .bak to a long-term file share, and deletes the segment from the live database. Microsoft documents the cold-archive workflow in Data Archiving Strategies for SQL Server; the recommended target for the cold copy is Azure Data Lake Storage Gen2 or an on-premises S3-compatible object store with object-lock enabled for audit compliance.

Verification and Commissioning

After the production event is wired, the following checks confirm end-to-end behaviour. Each check should be entered into the project SAT/FAT document with the timestamp and the operator's name.

1. Tag presence. In WinCC Explorer, open the tag list and confirm UA_ProductID, UA_MachineNo, UA_TimeStamp have a green status, not yellow or red.
2. Trigger event. Manually advance the PLC state from the STEP 7 / TIA Portal online test panel; verify the WinCC tag changes within 200 ms over the configured AS-OS connection.
3. Insert visibility. Open SQL Management Studio, connect to the WinCC instance, run SELECT TOP 10 * FROM dbo.UA_ProductionLog ORDER BY LogID DESC and confirm a new row appears within 2 seconds of the trigger event.
4. IT read path. From a workstation in the IT subnet, open Power BI Desktop > Get Data > SQL Server, enter the WinCC server name, choose Import mode, and confirm the table loads. If the connection times out, check the firewall rule on TCP/1433.
5. Backup job. Run the SQL Agent cold-archive job manually with EXEC msdb.dbo.sp_start_job N'WinCC_ColdArchive'; verify a .bak file appears in the target share and the segment is detached from the live instance.
6. Archive license. From the WinCC RT menu open Diagnostics > Licensing; confirm User Archives shows a green check, not the demo-mode amber bar.

Troubleshooting Matrix

Field-Proven Recommendations

For a four-PLC product tracking project where IT needs to read a product ID and exit timestamp, the path with the lowest total cost of ownership is User Archives plus the WinCC OLEDB Provider, with a SQL Server Agent job for daily cold archiving. The combination delivers:

• A single archive configuration in WinCC Explorer that runs at line speed with no additional tag licensing.
• A read-only API for IT that does not require OPC client installation on the IT workstation.
• Compliance with the ISO 22400 key performance indicator definitions, which expect per-piece traceability on a FIFO timeline.
• A clean handoff to a cold-archive store after 14 days without affecting runtime performance.

The custom SQL table is justified only when the project must record a BLOB, a JSON payload, or more than 256 characters per field. The alarm text channel is justified only when the volume is below 200 events per hour and the engineering budget excludes the User Archives option.

FAQ