Disabling SSIS Package Validation when Loading a Package in the VS Designer

Question

I have a few SSIS packages that are deployed to a SQL 2005 Server and are subsequently designed and maintained in Visual Studio 2003. When I open any of the BIDS projects, followed by one of the packages, the designer always validates every Data Flow and Task object.

Usually, this isn't a problem. However, under certain circumstances a system account that we use for connecting and export data from an AS400 will not validate properly. Since I'm exporting a TON of tables, each Data Flow object attempts to connect to the AS400 through an ODBC connection, it gets an "Invalid Account" error, and then proceeds to attempt validation of the next task. This locks the system account because the invalid credentials are tried 40+ times.

Is there something that I can do to disable validation until I try to run the package? As an alternative, is there a setting I can toggle that will stop all validation as soon one invalid object is found? The AS400 system will allow a few mistaken attempts before locking the account. Obviously, it would be optimal if I could cut off the validation process before that takes place.

What options do I have?

Solution 1

Sorry to be vague, but this is too long to copy/paste

"SSIS package taking forever to load when you open it?"

This describes these 3 options which affect loading speed + the validation

• DelayValidation property
• ValidateExternalMetadata property
• “Work Offine” option (SSIS menu -> Work Offline to enable/disable it)

You can also "DelayValidation" to in several other places too per connection/task

Solution 2

here's an alternative link (it seems the one GBN posted doesn't work any more):

http://www.jamesserra.com/archive/2011/07/ssis-package-taking-forever-to-load-when-you-open-it/

Hope it helps, Luka

Solution 3

A couple of years later, there was a new feature introduced in SSIS extension v3.9 which allows to skip validation when opening a package.

What's new: Allow users to skip validation when opening packages, which improves the performance. Please refer to this article for more details. https://techcommunity.microsoft.com/t5/sql-server-integration-services/accelerate-the-opening-of-ssis-package-in-ssdt/ba-p/1607099

---

Switch “Skip validation when opening a package” on/off To switch “Skip validation when opening a package” on/off, select the “Tools -> Options” item on SSDT menu and check/uncheck the “Business Intelligence Designers -> Integration Services Designers -> General -> Skip validation when opening a package” checkbox on “Options” window.

When the checkbox is checked, package validation will be skipped when opening the package. When the checkbox is unchecked, package will be validated when opened. By default, the checkbox is unchecked.

Comments

[RLH]

Thanks GBN. I think the "Work Offline" option seems like the best option b/c, if I'm understanding the documentation, when I cut off the "Work Offline" state, the project will still only validate one object at a time. Is that correct? Also, how do I implement this method? It's not mentioned in the article.

[RLH]

And one more question. Are their any gotchas that I should be aware of if I use this method?

[RLH]

Argh, having this issue again. I'm not asking you to further explain but the original link is broken. Would you happen to have a similar source that I could read through again? Thank you.

[OGHaza]

Good spot Luka, in the future you can click the edit button under a post and fix the link yourself - I've done this now

[RLH]

@OGHaza, I'm not sure he can. Note the rep of 1 (now 11 after my up-vote.)

[OGHaza]

AFAIK you don't need any rep to suggest an edit, anonymous users can suggest. Related Meta