We upgraded to the latest version of the CDATA last weekend. After that tasks that use environment variables for the ReplicateStartDate property are currently failing.
Current Configuration
Pre job event
<api:set attr="out.env:currenttimestampminus36months" value="[null | now() | dateadd('month', '-36', 'yyyy-MM-dd')]" />
<api:push item="out" />
Query:
REPLICATE [CTL].[AccountHistory] WITH ReplicateStartDate = '{env:currenttimestampminus36months}', ReplicateInterval = '1', ReplicateIntervalUnit = 'Months', DeletionBehavior = 'SkipDelete', DropTable = 'false' SELECT * FROM [AccountHistory]
Error:
[0] '{env:currenttimestampminus36months}' is not a valid value for the ReplicateStartDate property. We expect a date (yyyy-MM-dd) or an int.
Attempted Fix
Pre job event :
<api:set attr="out.env:currenttimestampminus36monthsModified" value="[null | now() | todate('yyyy-MM-dd', dateadd('month', '-36', 'yyyy-MM-dd'))]" />
<api:push item="out" />
Query:
REPLICATE [CTL].[AccountHistory] WITH ReplicateStartDate = '{env: currenttimestampminus36monthsModified }', ReplicateInterval = '1', ReplicateIntervalUnit = 'Months', DeletionBehavior = 'SkipDelete', DropTable = 'false' SELECT * FROM [AccountHistory]
Error:
[0] '{env:currenttimestampminus36months}' is not a valid value for the ReplicateStartDate property. We expect a date (yyyy-MM-dd) or an int.
Additional testing
We also attempted to filter the data directly in the query:
Queries :
REPLICATE [CTL].[AccountHistory] SELECT * FROM [AccountHistory] where Createddate >= '{env: currenttimestampminus36monthsModified }'
REPLICATE [CTL].[AccountHistory] SELECT * FROM [AccountHistory] where Createddate >= '2026-01-01'
Error :
[0] The column [CreatedDate] is not allowed in the WHERE clause of a replication
Questions
- Is ReplicateStartDate expected to support environment variable substitution?
- If so, what is the correct syntax for passing a dynamically generated date from a Pre-Job Event?
- Is there an alternative method to dynamically set the replication start date (e.g., using script variables or job parameters)?
Since CreatedDate cannot be used in the replication WHERE clause, what is the recommended approach for performing an initial load limited to the last 36 months?
