none
SSIS Package Validation in SQL 2008

    Question

  • We have an SSIS Package built in SQL 2005 and upgraded to SQL 2008. The data flow task uses a file connection manager as source. The value for this connection is built via a script task prior to the data flow task. The values used by the script tasks (path + filename) are in the SSIS Configuration table.

    I understand that if the connection manager's DelayValidation property is not set to TRUE, validation will occur prior to pacakge execution. Thus, given that the file connection path gets built at runtime, the value that will be validated will the the hardcoded value when the package was saved.

    We failed to consider this thus we deployed the packge to production (clustered) with DelayValidation=FALSE. Naturally, it failed with validation error (below) since the path is a dev path. But the thing is, the validation failure does not occur in our staging environment (stand alone server) despite the hardcoded path being non-existent (deleted).

    Is there something like a server-wide/global property for SSIS that controls this validation or a different behavior between stand alone and clustered server?

    Thanks!

    Error: "\\<sharedpath>\file.csv" specified in the connection was not valid.
    Connection "SourceFileConn" failed validation.
    The file name "\\<sharedpath>\file.csv" specified in the connection was not valid.
    The file name property is not valid. The file name is a device or contains invalid characters.

    *note: path is modified

    Tuesday, April 26, 2011 9:46 AM

All replies

  • 1) Is the package successful in the stand alone staging server?

    2) As per my knowledge there is no server wide global property for this. Even if there is one, those should be equal in both the servers right?

    3)The path in the stand alone server might have been deleted after the package is loaded in BI studio , but the package should ultimately fail if the path is not found.

     


    Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Tuesday, April 26, 2011 10:43 AM
  • Why not just upgrade the package in production to the 'latest and greatest'?
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, April 26, 2011 10:57 AM
  • 1) Is the package successful in the stand alone staging server? - Yes

    2) As per my knowledge there is no server wide global property for this. Even if there is one, those should be equal in both the servers right? - Yes, but then, the behavior seems to say otherwise.

    3)The path in the stand alone server might have been deleted after the package is loaded in BI studio , but the package should ultimately fail if the path is not found. - Yes, I have confirmed that the path no longer exists but it still does not fail. :(

     

    Tuesday, April 26, 2011 11:04 AM
  • What do you mean by the 'latest and greatest'?

    Tuesday, April 26, 2011 11:05 AM
  • 1) For the third point in my post above, can you open the package again in BI Studio and run it? Or may be correct me if you are running from SQL agent which might need a different resolution? 

    2) One thing to note is that, when the dev path is existent and open the package in BI studio , then you delete the path without reopening the package , the package cannot validate the deleted path. The package does not fail as its taking the values from config table which I wanted to correct in the 3rd point above.

     


    Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Tuesday, April 26, 2011 11:44 AM
  • What do you mean by the 'latest and greatest'?


    "We failed to consider this thus we deployed the packge to production (clustered) with DelayValidation=FALSE. Naturally, it failed with validation error (below) since the path is a dev path. But the thing is, the validation failure does not occur in our staging environment (stand alone server) despite the hardcoded path being non-existent (deleted)."

    So, to me, the solution seem to be to design the package with DelayValidation = True for that component, then deploy THAT version to the server. Am I over-simplifying things? Are there issues with this approach?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, April 26, 2011 12:01 PM
  • Yes, I agree. But the fact still remains that the package doesn't seem to be doing the right validation in the dev environment. This is the issue I'd like to address as we will not be able to catch similar issues in the future until deployed to production.
    Tuesday, April 26, 2011 3:32 PM
  • 1) For the third point in my post above, can you open the package again in BI Studio and run it? Or may be correct me if you are running from SQL agent which might need a different resolution?  -- I'm running from command line

    2) One thing to note is that, when the dev path is existent and open the package in BI studio , then you delete the path without reopening the package , the package cannot validate the deleted path. --yes, that's what we are expecting, it should not be able to validate the deleted path.

    The package does not fail as its taking the values from config table which I wanted to correct in the 3rd point above. -- the value is not in the config table as it is built via the script task in the package so I expect that it will be validating the non existent path specified in the package but it doesn't appear to be the case in our dev environment :(

    Tuesday, April 26, 2011 3:39 PM
  • Hi jade,

    1) As per your initial post "The values used by the script tasks (path + filename) are in the SSIS Configuration table." right?

    2) Are you applying the configurations from the command line ?(if at all you are using configuration for the package).

     3) Is the production box upgraded to SQL 2008? , there are behavioral changes to configurations in SQL Server 2008 and the same cannot be applied to SQL 2005. http://msdn.microsoft.com/en-us/library/bb500430.aspx bascially wehn executed from command line.


    Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Tuesday, April 26, 2011 4:27 PM
  • 1) As per your initial post "The values used by the script tasks (path + filename) are in the SSIS Configuration table." right? - yes, the path and filename are in the configuration table but the concatenation and assignment to another variable does not occur until the script task is executed.

    2) Are you applying the configurations from the command line ?(if at all you are using configuration for the package). - yes

     3) Is the production box upgraded to SQL 2008? , there are behavioral changes to configurations in SQL Server 2008 and the same cannot be applied to SQL 2005. http://msdn.microsoft.com/en-us/library/bb500430.aspx bascially wehn executed from command line. - thanks! i'll look into this

    However, this still doesn't explain why the validation error occurs in our 2008 clustered prod enviroinment but not in our 2008 dev solo box :(

    Wednesday, April 27, 2011 2:54 AM
  • I am facing same issue.

    SSIS 2005 package is deployed in Production last year, I checked last updated date, its Sept 2010.

    PROD Packages has developer machine values in configuration manager. Package is using value from PROD configuration file.

    Every thing was working fine till yesterday.

    Yesterday all packages failed in validation stage itself. I checked log files, it is validating to value in configuration manager (which is not exist now).

    Is it issue due to any Microsoft latest patch?

    What should i do now?

    Shall reopen package one bye one and change configuration manager values to PROD values

    Please help ASAP.

     

     

     

     

    Friday, April 29, 2011 5:56 AM
  • There is now an open source utility available in CodePlex which can be used to batch update SSIS Package Configuration File paths without using BIDS or manually editing the package XML:
    http://ssisconfigeditor.codeplex.com/
    Saturday, May 19, 2012 6:31 AM