SSIS DTS Variables do not work with dates


When setting a date variable through etl-run following exception occurs:
NBi.NUnit.Runtime.TestSuite.Clerk - Records in source = target:
Exception during the setup of the test:
[-1073450880] in Conditional Split [2] - The data types "DT_DBDATE" and "DT_WSTR" are incompatible for binary operator ">=". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

[-1073450876] in Conditional Split [2] - Attempt to set the result type of binary operation "FullDateAlternateKey >= @[User::TestDate]" failed with error code 0xC0047080.

[-1071615993] in Conditional Split [2] - Computing the expression "#27 >= @[User::TestDate]" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

[-1071615988] in Conditional Split [2] - The expression "#27 >= @[User::TestDate]" on "Conditional Split.Outputs[Case 1]" is not valid.

[-1073450901] in SSIS.Pipeline - "Conditional Split" failed validation and returned validation status "VS_ISBROKEN".

[-1073450996] in SSIS.Pipeline - One or more component failed validation.

[-1073594105] - There were errors during task validation.

at NBi.Core.Etl.IntegrationService.EtlRunner.Execute() in c:\Program Files (x86)\TeamCity\buildAgent\work\78c5267ceadfd0b6\NBi.Core\Etl\IntegrationService\EtlRunner.cs:line 24
at NBi.NUnit.Runtime.TestSuite.ExecuteSetup(SetupXml setup) in c:\Program Files (x86)\TeamCity\buildAgent\work\78c5267ceadfd0b6\NBi.NUnit.Runtime\TestSuite.cs:line 106

When running the same package through dtexec it works:
/FILE "\"C:\Users\richardk\Documents\Visual Studio 2013\Projects\Tipi-connect\Tipi-connect\Package1.dtsx\"" /CHECKPOINTING OFF /REPORTING EW /SET "\"\Package.Variables[TestDate].Value\"";"\"2014-10-07 14:55:57\""

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="Test" xmlns="http://NBi/TestSuite">
<test name="Clerk - Records in source = target">
    <etl-run path="C:\Users\richardk\Documents\Visual Studio 2013\Projects\Tipi-connect\Tipi-connect\" name="Package1.dtsx">
         <parameter name="User::TestDate">2014-10-07 14:55:57</parameter>


        <![CDATA[SELECT COUNT(*) From Dimension.Clerk WHERE StoreId = 8]]>

file attachments

Closed Jul 26, 2015 at 12:23 PM by Seddryck
Released in v1.10


Seddryck wrote Feb 13, 2015 at 6:06 AM

Hello Richard, I have not tested this feature with dates so I can't confirm the problem. Thx for the package, I will try to reproduce in the next days.

But, just to unblock you, are you sure the problem is not directly in your package itself?

richardkong wrote Feb 13, 2015 at 6:48 AM

The package is ok, as it works when running it through dtsexec with the same paramters. I also included the dtsexec command above.

I've worked around for now by using exe-run dtexec instead.

Seddryck wrote Feb 15, 2015 at 7:42 PM