Search code examples
visual-studio-2008expressionflat-filessis

Why is the variable in a package assigned to scope of another package in the solution?


So I've updated the DB source query in an SSIS package. But now the package fails at the destination. In debug, I get this output:

The file name property is not valid. The file name is a device or contains invalid characters.

and

No destination flat file name was provided. Make sure the flat file connection manager is configured with a connection string. If the flat file connection manager is used by multiple components, ensure that the connection string contains enough file names.

From what I've been able to research, I think the date variables are not casting properly. This is confusing because I have not touched the expression (except for the FileLocation variable) and it evaluates properly.

Here is the expression:

@[User::FileLocation]  + "\\GRADE_" + Right((DT_STR,4,1252) DatePart("yyyy",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + "_" + 
Right("0" + (DT_STR,4,1252) DatePart("Hh",getdate()),2) + 
Right("0" + (DT_STR,4,1252) DatePart("Mi",getdate()),2) + 
Right("0" + (DT_STR,4,1252) DatePart("Ss",getdate()),2) + 
Right("0" + (DT_STR,4,1252) DatePart("Ms",getdate()),2) + ".txt"

As suggested, I set the variable's EvaluatedAsExpression to True, but to no avail.

I tried hard coding the folder path instead of using a variable and that seem to solve the issue. But I need to use a configurable variable. What is the issue?

Update:

I noticed the scope of the variable I was using was in another package. How am I able to even view out-of-scope variables? If I have the correct package highlighted, and try to create a package level variable, its scope gets assigned as a different package?

enter image description here


Solution

  • Although I did not find the root issue of my problem (which I believe had something to do with my variable) my co-worker found a work around:

    After following the instructions that Siva gave in her answer (which corrected the scope of my variables, but did not solve the problem):

    My co-worker created a couple of extra string variables, one containing the folder path, and another which evaluated the file name. He also created a script task at the beginning of the package which then combined those two variables into a third variable.

    Then at the Destination Connection, the connection string expression contains the final concatenated variable. Voilà, the destination file name was accepted and the package completed successfully!