none
The connection is not found error

    Question

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

    Tuesday, January 07, 2014 7:06 PM

All replies

  • Question is whether the directory exists when the package runs already, I guess not. The DelayValidation part is turned off for the time the package is being loaded for verification/validation.

    Arthur My Blog

    Tuesday, January 07, 2014 8:38 PM
    Moderator
  • Are you saying that "DelayValidation" is false on load and execute, and true on when it applies to the task?  Is there a way to disable validation at the package level to check this? 

    Tuesday, January 07, 2014 8:43 PM
  • 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

    Tuesday, January 07, 2014 8:53 PM
    Moderator
  • This would be true, but in this particular package there is a Create Folder FileSystem task that creates it prior to the file being created/written and the folder does exist.  I didn't mention that as I didn't think it was related. 
    Tuesday, January 07, 2014 9:01 PM
  • Then set the DelayValidation property on the Flat File connection manager to True.

    Arthur My Blog

    Tuesday, January 07, 2014 9:15 PM
    Moderator
  • It already is set to true, and the ValidateMedia on the File task is set to False.
    Tuesday, January 07, 2014 9:19 PM
  • Please tell me how I could reproduce your issue

    Arthur My Blog

    Tuesday, January 07, 2014 9:38 PM
    Moderator
  • 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.
    Tuesday, January 07, 2014 9:58 PM
  • 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


    Tuesday, January 07, 2014 10:01 PM
    Moderator
  • Hi Tom,

    I have created two string type Package level variables (sDirectory and FilePath) and set their expressions as follows:
    sDirectory expression:

    "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) + "\\"

    FilePath expression:

    @[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.

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, January 13, 2014 8:42 AM
    Moderator