Configuring Periodic CSV Export from WinCC OLE DB Archive

Problem Overview

Engineers running SIMATIC WinCC Runtime or WinCC Professional frequently need to extract process values (flow rates, temperatures, pressures, alarm states) from the proprietary compressed archive on a fixed cadence and feed those values to downstream systems such as a historian, an MES layer, a reporting engine, or a custom analytics database. The simplest workflow reads the WinCC archive through the WinCC OLE DB Provider, returns a rowset with SQL, and either writes the result directly to a CSV file or hands the rowset to a SQL Server Integration Services (SSIS) package for transformation, file production, and incremental appending.

The symptom reported in the field is consistent: a one-shot manual export from the SQL Server Import/Export Wizard works, the file lands on disk, but the next run overwrites or ignores the existing CSV rather than appending new values, and the operator sees no incremental log. The root cause is structural, not a bug: the Import and Export Wizard is a designer tool. It does not natively maintain a growing file, and SSIS Flat File Destination components are configured for create/replace by default. This article documents the exact configuration that produces a continuously updated CSV from a WinCC archive, the VBScript alternative that is sometimes simpler, and the troubleshooting matrix that resolves the common failure modes.

Prerequisites

WinCC OLE DB Provider Architecture

The WinCC OLE DB Provider exposes the runtime archive to any OLE DB consumer on the same machine (and to remote consumers when the Connectivity Pack is licensed and the DCOM permissions are configured). The provider accepts standard OLE DB connection strings and translates a small SQL dialect into compressed archive reads.

Connection string template (local):

Connection string template (remote via Connectivity Pack):

The catalog name is visible in WinCC Explorer under Computer > Properties > Runtime Database and in the SQL Server Management Studio instance that ships with WinCC. The runtime database is shipped as CC_<ProjectName>_<YYYY-MM-DD>_<HH-MM-SS>; treat the suffix as fixed once the project is activated.

SQL dialect for archive reads

The provider recognizes two virtual table types: TAG:R (Tag, Realtime/Raw) and ALARM:. The most common hourly export for process data uses the TAG:R form.

Argument order: ArchiveName, TagName, StartTime, EndTime. Time literals are passed as YYYY-MM-DD HH:MM:SS.mmm in the WinCC server's local time. The returned columns are Timestamp (UTC, datetime), RealValue (double), Quality (int, OPC quality code), and

Flags (int). For multiple tags in one call, use the wildcard form:

For aggregated hourly rollups, use the TAE form (Tag Archive Extended) with explicit aggregate functions:

Step-by-Step: Build an Hourly Append-Ready SSIS Package

The first decision is the file-append model. Two patterns are field-proven and both are documented below.

Pattern A — Append to a single growing CSV (long-lived log)

1. Open SQL Server Data Tools (Visual Studio shell) and create a new Integration Services Project.
2. Add an OLE DB Connection Manager named cnxWinCC. Set the provider to Microsoft OLE DB Driver for SQL Server temporarily for the schema-build step, then reconfigure to Native OLE DB\WinCC OLE DB Provider at runtime. The package will read through this connection.
3. Add a Flat File Connection Manager named cnxFlowLog pointing at C:\WinCCExport\FlowHourly.csv. On the General page, select Create file only when needed off; this is the default. On the Columns and Advanced pages, configure the column delimiter as comma, the row delimiter as {CR}{LF}, the text qualifier as ", and the data type of every column to DT_STR (or DT_WSTR for Unicode). This avoids Excel locale-driven scientific-notation conversion of long flow values.
4. Drag a Data Flow Task onto the Control Flow. Open it and add an OLE DB Source. Set the data access mode to SQL command and paste the TAG:R query parameterized with SSIS variables.
5. Add a Flat File Destination pointing at cnxFlowLog. This is the critical step for append semantics: open the destination's Advanced Editor and set the component property OverwriteCreate (property name in the SSIS API) to 2 = CreateOnce + Append. The default is 0 = CreateAlways, which truncates the file on every run. Reference: Microsoft Learn — Flat File Destination > Custom Properties.
6. Return to the Control Flow and parameterize the time window. Create two SSIS variables of type DateTime: HourStart and HourEnd. Use an Execute SQL Task running UPDATE [?] SET ? = DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0); UPDATE [?] SET ? = DATEADD(HOUR, 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)); against a small parameter table, or simpler: use an SSIS Expression on the OLE DB Source that uses "..." + (DT_WSTR,30) @[User::HourStart].
7. Add a Precedence Constraint with an expression @HourStart < @HourEnd to prevent an empty query on a clock-skewed agent.
8. Save and deploy to the SSIS Catalog (SSISDB) or the legacy MSDB folder.

Pattern B — Unique file per run (preferred for immutable logs)

1. Same project skeleton as Pattern A, but the Flat File Connection Manager uses a property expression on the ConnectionString to inject a timestamp: "C:\\WinCCExport\\Flow_" + (DT_WSTR,30) YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR) MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR) DAY(GETDATE()),2) + "_" + RIGHT("0" + (DT_WSTR) DATEPART("hh",GETDATE()),2) + RIGHT("0" + (DT_WSTR) DATEPART("mi",GETDATE()),2) + ".csv".
2. Set the Flat File Destination OverwriteCreate property to 0 = CreateAlways (default) since each run has a unique path.
3. This pattern is the one most often recommended in WinCC community threads because it avoids the failure mode where a single growing file is locked by an open Excel session or a downstream service, causing the next run to abort with 0xC020200E or 0xC004701A.

Pattern C — VBScript in WinCC (no SQL Server required)

For self-contained WinCC Runtime projects, the WinCC Scripting Runtime can perform the export entirely inside the SCADA without a SQL Server agent. The script is attached to a cyclic trigger of 1 hour.

Scheduling the Periodic Run

SSIS packages live in three contexts. Pick the one that matches your infrastructure.

SQL Server Agent job definition for SSISDB:

USE msdb;
GO
EXEC sp_add_job @job_name = N'WinCC_HourlyFlowExport', @enabled = 1;
EXEC sp_add_jobstep @job_name = N'WinCC_HourlyFlowExport',
    @step_name = N'Run SSIS Package',
    @subsystem = N'SSIS',
    @command = N'/ISSERVER "\SSISDB\WinCCExport\Packages\Pkg_FlowHourly.dtsx" /SERVER "sql-host\wincc" /ENVREFERENCE 1 /Par "\$Package.HourStart" "2024-03-25 11:00:00"',
    @retry_attempts = 3,
    @retry_interval = 5;
EXEC sp_add_schedule @schedule_name = N'EveryHour',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 60;
EXEC sp_attach_schedule @job_name = N'WinCC_HourlyFlowExport', @schedule_name = N'EveryHour';
EXEC sp_add_jobserver @job_name = N'WinCC_HourlyFlowExport';

Comparison: SSIS vs VBScript vs Connectivity Pack

Troubleshooting Matrix

Performance and Capacity Considerations

Reading 10,000 tags for a one-hour window through the WinCC OLE DB Provider typically completes in 2-8 seconds on a WinCC V7.4 SP1 server with 16 GB RAM and a local SSD archive volume. The provider reads the compressed archive in a single thread; SSIS does not parallelize the OLE DB Source by default. For high-fan-out scenarios (>1,000 tags per hour), consider:

• Splitting the tag list into multiple TAG:R calls inside SSIS Sequence Containers and fanning out with a precedence constraint, then re-joining via Union All.
• Switching to the TAE aggregated query and pre-aggregating in WinCC with a Tag Aggregation of type Sum, Average, Min, Max per hour — the provider then reads only the rollup rows.
• Indexing the export target directory on the SSIS host by NTFS file system to avoid slow creation of millions of small files when Pattern B is selected.
• Compressing the export directory with NTFS compression or piping through gzip via a Script Task in SSIS to keep disk usage bounded for years of hourly history.

For reference, the SIMATIC WinCC Information System and the Siemens application example 38132261 benchmark a 5,000-tag hourly export at ~6 seconds for raw values and ~0.4 seconds for aggregated values on the recommended hardware.

Verification Checklist

1. Confirm the .dtsx package runs successfully from SSDT (one successful execution in the SSIS Catalog All Executions report).
2. Trigger the SQL Server Agent job manually; verify a new file appears in the export directory within the expected cadence.
3. Wait two consecutive hours; verify the file count or file size grows monotonically (append pattern) or new files appear with the expected timestamp pattern (unique-name pattern).
4. Open the latest CSV in a text editor and validate the header row, the column count, and that the Timestamp column advances by exactly one hour per row group.
5. Pull a row from the CSV into a SQL staging table and cross-check the value against the live WinCC online trend — values must match within the configured acquisition cycle (typically 500 ms or 1 s).
6. Force a WinCC Runtime restart; confirm the next scheduled run still produces a file. This validates the RetainSameConnection and catalog-name behavior across re-activation.
7. Inspect the SSIS Catalog All Messages view for the job to ensure no warnings (yellow triangles) remain that could become failures under load.

Field-Proven Pitfalls

• Catalog name changes after re-activation. Every time the WinCC project is reactivated, the runtime database name changes (the timestamp suffix updates). Hard-coded catalog names in connection strings break silently. Always derive the catalog dynamically from WinCC Explorer > Computer > Properties or query SELECT name FROM sys.databases WHERE name LIKE 'CC[_]%' on the bundled SQL Server instance.
• 32-bit provider on 64-bit OS without WoW redirection. The WinCC OLE DB Provider is 32-bit only on older WinCC versions (pre-V7.4). The 64-bit DTExec will fail to load it. Verify the bitness by inspecting the connection manager from the SSDT designer.
• TagLoggingFast acquisition vs TagLoggingSlow. The default cyclic archive is TagLoggingSlow at 1 s. Flow meters with 100 ms acquisition end up in TagLoggingFast and are not visible to the standard TAG:R query unless the correct archive name is supplied as the first argument.
• Time-zone DST. The hour window computed by DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0) returns the local clock hour, not a fixed UTC offset. On the spring-forward day the job runs 23 times; on fall-back it runs 25. Use UTC windows to get exactly 24 per day.
• Locking the CSV in Excel. If the user opens the unique-named CSV in Excel while the next hour's export is being written, the write fails with 0x80070020 (sharing violation). Switch to the append pattern or use a staging directory that is rotated on completion.

Architectural Diagram

References in Context

The architectural foundation is documented in the Siemens TIA Portal Help for Access to archive data via WinCC OLE DB Provider (RT Professional) and the classic WinCC V7 Information System. The application example at Siemens Support 38132261 provides the three canonical architectures: direct OLE DB, Connectivity Pack relay, and the SQL Server / SSIS route discussed in this article.

FAQ