The SQL Girl

Just another SQL geek

Tag Archives: validation

Menu

Skip to content
  • Home
  • Blog Posts
  • Events
  • SQL Tools
  • Consulting Services
  • Data Community Involvement

SSIS Package Validation

September 17, 2016 by Jeanne Combrinck Posted in Business Intelligence, SQL Tips, Visual Studio Tagged Data Tools, etl, ETLs, Metadata, Microsoft SQL Server, SQL, SSDT, SSIS, SSIS Package, SSIS Validation, validation, Visual Studio 8 Comments

Ever wonder why your SSIS packages are taking forever to load? This is because SSDT is trying to validate your packages. What happens is SSIS checks the data sources in the package(s) that are open and checks to ensure that the external metadata is valid. If the metadata is not valid you will get error/warning messages.

You can spend quite a lot of time waiting for SSIS to validate your packages. This can be due to the fact that you have many data sources and components in your package, or slow network connectivity.

When you are in the process of developing you might want to delay the validation. There are three different methods to delay validation, Delay Validation property, Validate External Metadata property and the “Work Offline” option.

Delay Validation:
You can set the DelayValidation property to true at a package level and at a task level.

Package Level
packagedelay
Task Level
taskdelay
Note that setting the DelayValidation property to true at package level does not prevent the initial validation when you open the package, it simply delays the validation until you execute the package.

If you want to delay the validation when the package opens, you will want to set the DelayValidation property to true at task level.

Validate External Metadata:
The DelayValidation property can be set at task level but not on individual components within the task, this is where the ValidateExternalMetadata property comes in. You can set the ValidateExternalMetadata property to false on a Data Source. By doing this the component will not be aware of changes to the metadata or external data sources. By overriding the default value and setting the property to false, SSIS will not validate the data source until the component is run.
externalmetadata

Work Offline:
You can turn off validation by selecting the “Work Offline” option on a connection.
workoffline
It validates what can be validated locally without connecting to a database, such as flat file connections where it will check if the file exists.

Most of the validation time is spent connecting to external data sources, therefore the “work offline” option is very fast. Be aware that you will get validation errors while you are working offline.

Tip:
SSDT remembers all the packages you had open the last time you closed the application. Therefore, when you open your project it will validate every connection in all those packages and you could be stuck for a long time waiting for SSDT to respond. It is recommended that you close all open packages before closing SSDT.

d7ec408f831610926b7047e7e551f86d

 Subscribe to my feed

SQL Saturday Cape Town
MCSE BI
MCSE Data Management & Analytics

Widget Ready

This left column is widget ready! Add one in the admin panel.

Recent Posts

  • PowerBI – Pivot and Unpivot Columns
  • PowerBI – Constant Line
  • PowerBI – Table Heat Map
  • PowerBI – Turn on Preview Features
  • Creating PowerBI Layouts
  • Pivot and Unpivot
  • Merge Statements
  • Querying Data in Temporal Tables
  • Modifying Temporal Tables
  • Temporal Tables

Categories

  • Business Intelligence
  • DBA
  • Exams
  • FileTable
  • Management Data Warehouse
  • MDS
  • Performance Tuning
  • PowerBI
  • Reports
  • SQL Tips
  • SSIS
  • SSRS
  • System Tables
  • Uncategorized
  • Visual Studio