SSIS File System Task error: The type of the value being assigned to variable ... differs from the current variable type

Question

OK... I have a relatively simple SSIS package (DTSDesigner100, Visual Studio 2008 Version 9.0.30729.4462 QFE, .NET Framework 3.5 SP1). At the end of the SSIS control flow for the package is a File System Task which is a Rename File operation.

The properties of this File System Task are as follows:

• IsDetinationPathVariable: True
• DestinationVariable: User::OutputFileName
• OverwriteDestination: True
• Description: File System Task
• Operation: Rename file
• IsSourcePathVariable: False
• SourceConnection: Excel Template Copy Destination

There are no defined expressions. As you can see, I have the Destination assigned as a variable User::OutputFileName. So let's look at that variable...

• Name: OutputFileName
• Scope: Package
• Data Type (ValueType): String
• Value:
• Namespace: User
• EvaluateAsExpression: True
• ReadOnly: False
• Expression: (see below)

.

"\\\\SERVER\\Folder\\" + "MyAwesomeExcelWorkbook_"
+ (DT_WSTR,4)DATEPART("year", GETDATE())+ "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mm", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("dd", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)
+ ".xlsx"

So I can Evaluate this expression successfully. Every time the package runs (and fails) its Value is the value of the evaluated expression, as one would expect.

When I run the package, here's what happens...

1. The previous steps run successfully.
2. The File System Task starts, and the file is successfully renamed.
3. However, the package now fails since the File System Task fails with the following error:

.

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::OutputFileName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Task failed: Rename Excel Workbook
Error: 0xC0019001 at Rename Excel Workbook: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.

There is apparently some minor detail I am forgetting here. Now, I've tried casting the entire expression as a DT_WSTR,512 -- that evaluates just fine, but the File System Task will still fail with the same error.

Anyone who can point out the detail(s) I'm overlooking? Thanks in advance.

Solution 1

The issue is the variable is scoped to the Package, and not the task.
Changing the variable to be scoped to the task results in successful execution.

EDIT: I figured I need to ask -- why? This honestly doesn't jibe with my understanding of SSIS -- which I've used since its introduction through every Denali CTP. What is going on under the hood that results in this being hunky-dory for task-scoped variables, but not package scoped ones that get used in a File System Task? Outside of certain tasks there's no issue, but there is one when I use them in certain tasks. What's going on?

Solution 2

I also encountered the same error. I was using an "Execute SQL Task" to run a stored procedure, from which the single row result set values were being used to set the SSIS package variables. The variables from the stored procedure were declared as "varchar(max)" and I was assigning them to package level variables which were defined as "string".

So I tried changing the stored procedure declarations to varchar(512) and "VOILA"... no more error. Actually... it also worked with varchar(8000) and nvarchar(4000)... but SSIS no likey varchar(max).

Comments

[The Lazy DBA]

Not the case in this situation.

[celerno]

This doesn't makes sense at all. I need a variable to be used at two different tasks. Anyway, thanks for your answer. Had helped me finding a different workaround.

[Heinz Siahaan]

You save me. I don't know why SSIS don't support varchar(max), but support 8000 and less.