Go SSIS package taking forever to load when do you open it?

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

Posted on July. by James Serra

When you open a SQL Server integration Services (SSIS) package in SSIS Designer or add components to a package, SSIS by de Fault checks the data sources used by the various components. This process for SSIS package validation ensures, the external metadata are valid.  If the metadata is not valid, You'll receive warning or error messages pointing to the problem.  in times, you might want to override the default B Ehavior and open the SSIS package without validation because the validation process can take a really long time if you hav e Many data sources and/or some of them is to remote servers with a slow network in between.  If you ' re in the Proce SS of developing a package and you find yourself opening the package often, you can spend an inordinate amount of time WA Iting for SSIS to validate sources.  It would is nice if SSIS used a background thread to validate, and let you Go about your business, but it instead it freezes the UI and does don't let's do anyThing else.

There is three options around this problem:the delayvalidation property, the ValidateExternalMetadata property, and the "Work offine" option.

DelayValidation Property:

Set the DelayValidation property to True to prevent validation at design time to improve performance. This property was also used on the package elements whose configuration isn't valid at design time to prevent validation error S. For example, the May has a Data Flow task that uses a destination table This does not exist until an Execute SQL task  Creates the table at run time. The DelayValidation property can is enabled at the ' package ' or at the level of the individual tasks and containers th  At the includes of the package (the available to individual data flow). Normally must leave this property set to True on the same package elements when you deploy the package to prevent the Same validation errors at run time.

Setting the DelayValidation property to True on the task level can is one of the most time-saving steps you take.

Note that setting the DelayValidation, The package level does not prevent the initial validation proce  SS when your open the package.  It simply delays any package-level validation if you run the package.  That's means that's the package would still run, but a warning would still appear when you open the package.  This was important if you also want to prevent the validation when the package opens. In this case, you should set the DelayValidation property to True at the tasklevel.

ValidateExternalMetadata Property:

As previously mentioned, the DelayValidation property can is set on a Data flow task, and not on individual data flow comp  Onents. You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components (i.e.  OLE DB Source component) to False. However, when the value of the "is" False, the component is not aware of changes to the metadata of external data Sources.

By default, the ValidateExternalMetadata property was set to True and so SSIS validates the external metadata whenever you ope n the package, add a component, or run the package. If you override this behavior by setting the property to False, SSIS won't validate the data source until it runs that Component.

"Work offine" option:

It is possible to turn off validation by selecting ' work Offline ' from the SSIS menu.  It still validates what can is validated locally without connecting to databases, such as checking file existence. Still, since most of the validation time was spent connecting to external data sources, the offline validation is very fast  . Just be aware so you'll get validation errors.

Unlike the DelayValidation and ValidateExternalMetadata properties, the work Offline option are available even before you O Pen a package.

When your is ready-to-run the package, you'll need to turn off ' work Offline '.  And fortunately it won ' t try to validate all the connections when you do turn if off. It would only be validate a task when it was about to execute the task or if it's a data Flow task and you open it.

Be aware this if you had "work Offline" selected and open a data flow tasks, it still won ' t try to validate the connection S. and tasks that is disabled would still be validated if "work Offline" is turned off.

Notes:

If database objects that is used by the package is locked when validation occurs, the validation process might stop RESP  Onding.  In these circumstances, the SSIS designer also stops responding.  You can resume validation by using Management Studio to close the associated session in SQL Server. You can also avoid this issue by using the three options described in this section.

It's a good idea to close all tabs before exiting Visual Studio, as it would remember what packages you had open and then O  Pen those packages again when you open the project next time. So if you don't have "work Offline" selected, and has tabs open with a lot of connections, the next time you open that PR Oject all those connections'll be a validated and you'll be stuck waiting for it to finish.

More info:

Open SSIS packages without validation using these SQL properties

Troubleshooting Design-time Validation Issues

Go SSIS package taking forever to load when do you open it?