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?
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?
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!