I have an expression variable that defines the root folder of subsequent file variables. The expression for the root folder variable (@[User::sDirectory]) is basically "C:\\folder\\" + YYYY + "\\" + MMDD + "\\" which evaluates to "C:\folder\2014\0104\"
When combined to create a file variable "@[User::sDirectory] + "ErrLog\\log.txt", it evaluates to "c:\folder\2014\0104\ErrLog.txt"
This file variable is used as the ConnectionSring value in a flat file connection. The connection itself is called "FlatFile" (for simplification).
When I run the package, I get a "The connection 'C:\folder\2014\0104\' is not found. As though it's interpreting the resolved root folder variable as a connection.
I have "ValidateExternalMedia" off on the File tasks and "DelayValidation" as true on the connection that uses it (FlatFile).
Anyone know why it thinks that my variable is a Connection?
You parametrized the connection via a variable - once the package begins running and has passed the validation phase successfully due to the DelayValidation is being false it dies because the actual directory does not exist at the point in time the Flat File Connection manager is initialized.
Arthur My Blog
It seems that moving the expression logic from a variable being used by the flat file connection for it's connection string to the connection itself cleared up the problem. It's not what I want, I preferred to have the flexibility of changing it in the variable.
A package variable can have an expression attached to it -- F4 - Evaluate as Expression TRUE, and create the expression that you can use to drive the variable which in turn becomes driving the Flat File Connection manager connection string --> this is the one controlling the file placement
Arthur My Blog
- Edited by ArthurZMVP, Moderator Tuesday, January 07, 2014 10:02 PM
I have created two string type Package level variables (sDirectory and FilePath) and set their expressions as follows:
"C:\\folder\\" + (DT_WSTR, 4)DATEPART("yyyy", GETDATE()) + "\\" + RIGHT("0" + (DT_WSTR, 4)DATEPART("mm", GETDATE()),2) + RIGHT("0" + (DT_WSTR, 4)DATEPART("dd", GETDATE()),2) + "\\"
@[User::sDirectory] + "ErrLog\\log.txt"
After that, I set the ConnectionString of the Flat File Connection Manager to User::FilePath, and the package ran successfully.
The issue may be caused by the sequence of the Control Flow Tasks in your workflow which caused the validation of the ConnectionString to kick off before the File System Task created the corresponding folder. You can create a simple package with only the Data Flow Task and the two variables to check whether the ConnectionString of the Flat File Connection Manager works. If it is related to the design of the Control Flow Tasks, I suggest that you post a screenshot of your Control Flow Tasks as well as the Data Flow Task to make further analysis.
TechNet Community Support
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, January 16, 2014 5:45 PM