none
SSIS package hangs on validation in Visual Studio

    Question

  • When I push my SSIS packages up to my production server (which has a different data source than my developement environment) and I try to open the package on the production server, it takes forever for to validate all the steps of the SSIS package because it's trying to validate against a datasource that isnt there, so it just waits for each element it's validating to time out. This is exceptionally annoying.

    Is there a way to turn off this validation 'feature'?
    Friday, January 19, 2007 6:22 PM

Answers

  • There is a Work Offline option on the SSIS menu. Select that before you open the package.

    That may solve the immediate issue, but why do you need to open packages on a production server that just seems wrong. You should use something like configurations to ensure that connections are set externally from the package and just work when you migrate between environments.

    Friday, January 19, 2007 7:42 PM
    Moderator

All replies

  • There is a Work Offline option on the SSIS menu. Select that before you open the package.

    That may solve the immediate issue, but why do you need to open packages on a production server that just seems wrong. You should use something like configurations to ensure that connections are set externally from the package and just work when you migrate between environments.

    Friday, January 19, 2007 7:42 PM
    Moderator
  • I think it's Microsoft oversight to release product, that hangs IDE.
    Its immposible to use, because of it  constant hangs. For example, i took package to work home. It hangs Visual studio. Is it good?
    Why not include button to validate package at the end of work (manually)?
    Automatic validation such a nasty thing, that i prefered to use manual coding af a program for ETL task.
    Don't recomed anybody to use such a user unfriendly product.
     
    Saturday, August 22, 2009 6:40 PM
  • The reason the IDE hangs is that the validation happens on one main foreground thread, and that is not a background thread which allows you to browse your package while it validates.

    As the SQL product group works on the next version of SQL, I put in an ask to get validation in the design surface to not hang.

    Its not as easy as you might think, since some components should not be edited if validation has not had a chance to run.

    I agree a cancel button to kill validation when your package refers to an object which is not available.

    Submit the idea at Connect.microsoft.com to see if the product group will give you further feedback.

    SSIS menu > WorkOffline is about the only way to prevent the hang. Its equivalent to doing "disconnected edit" in DTS 2000. However, some screens in some components cannot be opened when working offline.

    Thx, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Saturday, August 22, 2009 9:37 PM
  • The problem can be partially solved by checking first connection.
    If first check of connection is not validated, why to try validate it in every component that uses it?
    The othe problem with working offline is inability to access properties of some components.
    For example i try to open lookup task, the second tab can not be opened because it tries to obtain metadata through connection. I dont undestand why to do it, because lookup task was already configured on onathe machine. All i wanted to do is to inspect how it was previously configured.
    One of the next problems is data type conversion. Even varchar types must have the same length. And even varchar and nvarchar is different. Practically it is mostly unusable. Why not allow automatic coversion (transact sql allows such thing)?

    P.S.- i dont know a person who would be satisfied with this product. Do you know some?
    Sunday, August 23, 2009 8:02 AM
  • Now i'm trying ssis2008, converted my simple package from version 2005 to 2008.
    Made setting work offline, all tasks have delayedvalidation set to true.
    Open lookup task on connection tab, ide hangs permanently without timeout:( Needed to kill VS2008.

    I'm very intrested was this product tested?
    It is good to ask Bill Gates to do simple tasks with this product.
    Would be glad to look at his face:)

    • Edited by BINGO BONGO Sunday, August 23, 2009 9:33 AM
    Sunday, August 23, 2009 9:31 AM
  • Posted bug report
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=485347

    Please vote.
    Wednesday, August 26, 2009 6:00 PM
  • SSIS menu > WorkOffline is about the only way to prevent the hang. Its equivalent to doing "disconnected edit" in DTS 2000. However, some screens in some components cannot be opened when working offline.

    Wont do. The SSIS menu isn't available until after I select to open the project. At that point it's already hung.
    Tuesday, September 1, 2009 7:28 PM
  • The SSIS menu becomes available when you are in solution explorer on an SSIS project and click on a package. Before opening it you can select Work Offline, then double click to open the package.
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Proposed as answer by jonbatist Wednesday, December 2, 2009 11:56 AM
    Thursday, September 3, 2009 12:45 AM
  • I have same problem, and watched an interesting action.

    When the package is validating and vs is locked, on mssql there is a conection working from vs, doing a select * from [table], this table is a source from a dataflow.

    But this table is so large, and vs do a select * from and this is the hang cause.

    I still not found a solution, just work offline or clear table before open vs.

    that's bad.

    (sorry my english)

    Jonas
    Wednesday, December 2, 2009 12:00 PM
  • A colleague of mine is working on a bug right now where DB2 provider does run the full SELECT * FROM TABLE during SSIS package validation because a certain kind of server side cursor is not supported by that provider. We are working on that fix, but it is not out yet.

    Are you talking about a connection using a 3rd party provider or a SQL Server provider is running the full SELECT * query during validation?

    If the words SET FMTONLY ON are in the same batch, the SQL engine does not run the query, it only returns the metadata - a trick some providers use to get metadata for the columns.

    Another trick providers use is SELECT * FROM TABLE WHERE 1=0 so that no rows are returned, but the column metadata is returned to the provider, which is handed to SSIS.

    Thanks, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Wednesday, December 2, 2009 2:47 PM
  • When it releases, try the fix described in http://support.microsoft.com/kb/975950 since this fixes a performance problem in validation running the whole query for some database providers. This ships soon in SP3 CU7 later this month.

    Thanks, Jason

      
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Tuesday, December 8, 2009 10:01 PM
  • MSFT never fails to impress me, 5 years later this bug still exists, I have an SSIS project with OLE Destination to a table that is currently running a rebuild of an index. So the validation of this SSIS package hangs the whole Visual Studio 2013. (and no no cancel button) It's a joke but a bad joke.
    Tuesday, June 24, 2014 6:38 AM
  • There's a quick method to release this validation.  Kill the thread that is reading the database.  SP_Who will do.
    Wednesday, October 14, 2015 12:00 AM
  • WOW. Your recommendation saved me in Jan 2018. Thanks once again
    Thursday, January 18, 2018 2:15 AM
  • Actually, this freezing can work in your favor.  The project that confounded me the most would open every time but the package was failing in other environments.   When examined closely, the variable being pulled was a user variable and not a package variable.  Once the package variable was set, the IDE displays the freeze feature.   It is my way of knowing the problem is fixed.
    Saturday, May 18, 2019 4:38 PM