CODEWITHSUNDEEP
ssis
Cheyne
Cheyne

Reputation: 2137

SSIS User Variable Assignment

Does anyone know how to assign one user variable to another in script in SSIS?

I have two user variables, both of them are DateTime type.

I try to assign one to the other in a script task and I get an invalid type error

Dts.Variables["Query_AccountingDay"].Value = Dts.Variables["Override_AccountingDay"].Value;

Gives me the error: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The type of the value being assigned to variable "User::Query_AccountingDay" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Iv tried casting, that doesnt work.

Help please

Upvotes: 2

Views: 9941

Answers (2)

Ned
Ned

Reputation: 29

The Query_AccountingDay variable has to be specified as a ReadWrite variable, not a ReadOnly variable as you had specified.

Hope this helps

Ned

Upvotes: 0

billinkc
billinkc

Reputation: 61269

So you've tried?

Dts.Variables["Query_AccountingDay"].Value = Convert.ToDateTime(Dts.Variables["Override_AccountingDay"].Value.ToString());

If that isn't working, mind grabbing a quick screenshot of the variables window so that we can verify data types?

Mine looks like this, Override value is 2007-07-07, Query is 2011-10-19

Variables window

Script task configured thusly Script task variables window

Body of the code looks like this

    public void Main()
    {
        MessageBox.Show(String.Format("{0}:{1}", "Override_AccountingDay", Dts.Variables["Override_AccountingDay"].Value));
        MessageBox.Show(String.Format("Before {0}:{1}", "Query_AccountingDay", Dts.Variables["Query_AccountingDay"].Value));

        Dts.Variables["Query_AccountingDay"].Value = Convert.ToDateTime(Dts.Variables["Override_AccountingDay"].Value.ToString());
        Dts.TaskResult = (int)ScriptResults.Success;

        MessageBox.Show(String.Format("After {0}:{1}", "Query_AccountingDay", Dts.Variables["Query_AccountingDay"].Value));
    }

Execution results via message boxes

---------------------------

---------------------------
Override_AccountingDay:7/7/2007 7:37:36 AM
---------------------------
OK   
---------------------------

---------------------------

---------------------------
Before Query_AccountingDay:10/19/2011 7:37:41 AM
---------------------------
OK   
---------------------------

---------------------------

---------------------------
After Query_AccountingDay:7/7/2007 7:37:36 AM
---------------------------
OK   
---------------------------

Upvotes: 4

Related Questions