CODEWITHSUNDEEP
sql-servervisual-studiosql-server-2005ssisbids
RLH
RLH

Reputation: 15718

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

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?

Upvotes: 25

Views: 62530

Answers (4)

P-L
P-L

Reputation: 533

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.

enter image description here

Upvotes: 2

gbn
gbn

Reputation: 432722

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

Upvotes: 30

Sara
Sara

Reputation: 19

Right click on a Project Solution-> Property-> Configuration Properties-

Debugging-> Interactive Mode-> False

Upvotes: 0

Luka Lovosevic
Luka Lovosevic

Reputation: 41

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

Upvotes: 4

Related Questions