1-800-264-9029|602-266-8500
  • Course Schedule
    • Microsoft Training
      • Windows 10
      • Teams / Microsoft 365 / MS365
      • Windows Server 2019
      • Windows Server 2016
      • SQL Server
      • Power BI
      • Power Platform
      • Azure
      • Data Engineering / Machine Learning
      • Artificial Intelligence (AI)
      • Exchange Server
      • PowerShell
      • System Center
      • SharePoint
      • IIS
    • Cloud Computing
      • Azure
      • Amazon Cloud (AWS)
    • Developer Training
      • DevOps
      • Docker Kubernetes
      • Web Development
      • .NET Development and Visual Studio
      • Java Programming
      • Python
    • Cisco Training
      • Cisco CCNA Routing & Switching
      • Cisco Training – HD TelePresence
    • CompTIA Certification
      • CompTIA A+
      • CompTIA Network+
      • CompTIA Security+
    • Business Training
      • Project Management
      • ITIL
      • NIST
      • Business Analysis
      • Agile
    • IT Security Training
      • CISSP / CEH / PKI / Security
      • NIST
    • Wireless / Wireshark Training
      • Wireless Networking
      • Wireshark
    • Red Hat
      • Red Hat – Linux
      • Red Hat – DevOps
    • Other Training
      • VMware
      • NetApp
      • F5 Networks
      • Salesforce
      • Citrix
  • Subscription Schedule
  • RemoteLive™
  • Replay™
  • Video Courses
  • Blogs
    • Tech Blogs
    • Tech Videos
  • About
    • Instructors
    • Interface Gold™ Benefits
    • TechPaks
    • Our Video Training Timeline
    • Training Room Rental
    • Group Training & Private Classes
    • Contact

Working with Validation in SSIS

Home > Blogs > SQL Server > Working with Validation in SSIS

Working with Validation in SSIS

Like This Blog 1 Peter Avila
Added by Peter Avila June 13, 2012

SSIS validates packages to ensure that they will execute properly. SQL Server Validation occurs both as a package is opened in the BIDS design environment (Design-Time Validation) and just before the package executes (Run-Time Validation). Validation is very useful because it can help find errors in a package, usually with its connections, before it gets into trouble during run-time, but there are situations in which validation is not desirable. Here are three of the most common along with a recommended solution to address each:

Design-Time Validation

The following two examples illustrate problems validating a package when it is opened in BIDS.

1. The package contains many connections, and each time you open the package in BIDS, you must wait several minutes for the validation to complete. You know that the connections are set up properly and you would like to just have the package load rather than wait several minutes.

2. The package contains one or more connections to data sources and/or destinations that are temporarily unavailable during design time, resulting in errors and/or, again, lengthy load times when the package is opened.

SOLUTION to 1 and 2: Work offline. By telling BIDS that you are working offline before you open a package, BIDS prevents SSIS from validating the package as it is opened.

To work offline, you must first open a solution. Once the solution has opened, the SSIS menu in BIDS becomes available. Select Work Offline from that menu before you open the package.

SQL 2008R2 working with validation SSIS offline

 

 

 

 

 

 

 

Keep in mind that while you are offline you cannot execute the package. So, after the package opens, you can select the same option from the SSIS menu to go back to working online. This allows you to skip the validation and go back to working as you normally would have. Also, Work Offline is only available in design-time and has no effect on the package when it is executed outside of BIDS, such as in a SQL Server job, when executed using DTEXEC, and so on.

Run-Time Validation

Run-time validation occurs when the package executes, whether it executes in BIDS or outside of BIDS after deployed to a production environment. The following example illustrates problems validating a package when it is asked to execute. Note that validation occurs before the package executes.

3. Let’s suppose you have an Execute SQL task that creates a table. Following that task is a Data Flow task that contains a data source component that opens that table. The first time you execute the package, the table won’t yet exist; so, in the validation phase, SSIS will raise an error on the component that tries to access the as-of-yet non-existent table. Remember: The validation phase comes before the package executes.

In examples 1 and 2, above, the solution was to Work Offline, which can also help in this situation, but only in BIDS and not in a production environment! The problem with Work Offline is that it is an option in BIDS only and is not available (or desirable!) when the package executes in its production environment after it has been deployed.

SOLUTION: Delay the validation of the task or data flow component causing the validation error. Delaying validation tells SSIS to validate the task or data flow component when the package executes, rather than validate it in the validation phase that occurs just before the package executes. In this example, because the package is able to execute, the table is created, and by the time execution reaches the component that opens the table (the component that was causing a validation error), there will no longer be any reason for SSIS to raise an error since the table will be in place.

Validation can be delayed at the package level, the control flow level, or the component level (inside a data flow). To delay validation of an entire package, change the package’s DelayValidation property to True. Doing this will delay validation of all tasks and components in the package.

SQL 2008R2 DelayValidation working with validation

 

 

 

 

 

 

 

To delay validation of a control flow task such as an Execute SQL task, a Data Flow task, or other, change the task’s DelayValidation property to True. If the task is a Data Flow task, then all of its components will have their validations delayed, too.

Finally, to delay validation of a single component in a Data Flow, change its ValidateExternalMetaData property to False.

SQL 2008R2 ValidateExternal Metadata working with validation

 

 

 

 

 

 

 

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Subscribe to this author's posts feed via RSS

You May Also Like

  • 20463 MOD: Implementing a Data Warehouse with Microsoft SQL Server 2012 & 2014 (On-Demand)20463 MOD: Implementing a Data Warehouse with…
  • IPv6 Port RedirectionIPv6 Port Redirection
  • Exploring Join Paths; The Key to Building Multiple-Table JoinsExploring Join Paths; The Key to Building…
  • 3 Problems That Occur When Deploying in the 2.4 GHz Band in WiFi Networks3 Problems That Occur When Deploying in the 2.4 GHz…
Category SQL Server

Tags

BIDS, DelayValidation, Design-Time Validation, ExternalMetaData, Run-Time Validation, SQL, SQL 2008, SQL Server, SQL Server Validation, SSIS

Videos You May Like

Agile Methodology in Project Management

Agile Methodology in Project Management

0 162 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

JavaScript for C# Developers – September 24, 2014

0 491 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 645 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

See what people are saying...

    Share your thoughts... Cancel reply

    Please fill out the comment form below to post a reply.

    ITIL 4 Promotion

    Blogs by Technology

    • Amazon AWS (2)
    • Azure Cloud Computing (2)
    • Business Analysis (8)
    • Cisco (125)
    • CompTIA (71)
    • Developer Visual Studio / ASP.NET (62)
    • Exchange Server (47)
    • ITIL / COBIT (33)
    • Lync Server (3)
    • Office 365 (5)
    • PMP Project Management (63)
    • PowerShell (81)
    • Security (47)
    • SharePoint (93)
    • SQL Server (78)
    • System Center (13)
    • Web Development (60)
    • Windows 10 (65)
    • Windows 7 (124)
    • Windows 8 (85)
    • Windows Server (74)
    • Windows Server 2012 (90)
    • Windows Server 2016 (14)
    • Wireless (9)
    • Wireshark (5)

    Blogs by Instructor

    • Dan Wahlin (44)
    • Don Jones (15)
    • Dr. Avril Salter (6)
    • Greg Richard (1)
    • Interface (49)
    • Jason Helmick (38)
    • Jeff Jones (18)
    • Jeremy Cioara (8)
    • Mark Jacob (125)
    • Mark Thomas (28)
    • Mike Danseglio (93)
    • Mike Pfeiffer (35)
    • Peter Avila (32)
    • Rick Trader (127)
    • Spike Xavier (86)
    • Steve Fullmer (163)
    • Suzanne Van Hove (1)

    Blogs You May Like

    • How to Configure Navigation in SharePoint Publishing Sites
    • Using Navigation Controls in a Collaboration Site in SharePoint
    • Forensic Investigation of Malware – What’s going on Behind the Scenes
    • ECMAScript 6 (ES6) – The Future Look of JavaScript for C# Developers
    • JavaScript for C# Developers – Differences between JavaScript Dynamic Syntax and C#
    • JavaScript for C# Developers – Key concepts of C# and JavaScript Syntax

    Video Courses

    • ITIL 4 Foundation Certification Video Training Course
    • Project Management Professional (PMP®) Certification Video Training PMBOK® 6th Edition
    • PMI-PBA Business Analysis for IT Analysts and Project Managers (PMI-PBA)® Certification
    • SharePoint Designer 2013 for American Express
    • CompTIA Network+ (Coming Soon!)
    • CompTIA Security+ (Coming Soon!)
    • CompTIA A+ Certification Core 1 1001 (Coming Soon)
    • CompTIA A+ Certification Core 2 1002 (Coming Soon)

    Live Training Courses

    • NET+007: CompTIA Network+ Certification Training + N10- 007 Exam
    • PowerShell - 10961: Automating Administration with Windows PowerShell
    • ITIL4® Foundation Certification Course with Exam
    • AZ-100: Azure Infrastructure and Deployment Training
    • PMI-PBA: Business Analysis for IT Analysts and Project Managers (PMI-PBA Certification)
    • Cisco CCNA - ICND1v3 Interconnecting Cisco Networking Devices CCNA Part 1
    • COBIT205: COBIT® 5 Foundation and Implementation IT Governance Training
    • DEV415: Microservices with ASP.NET Core and Docker
    • IT Security - SEC+501: CompTIA Security+ with Certification Exam SY0-501
    • SQL Server - SQL101: Introduction to Transact SQL
    Facebook
    Twitter
    Linked In
    Comment
    EMAIL

    About Us

    • Interface Gold™ Benefits
    • TechPaks
    • Training Room Rental
    • Group Training & Private Classes

    Contact Us

    3115 N 3rd Ave
    G130
    Phoenix, AZ 85013

    602-266-8500

    Stay in touch on our social channels!
    Twitter
    Facebook
    LinkedIn
    State of Arizona Contract # ADSPO18-210228

    Copyright © 2021 Interface Technical Training. All Rights Reserved.

    Live Training Terms and ConditionsTerms of UsePrivacy PolicyWIOA Policy

    We are OPEN! All live classes 100% available with RemoteLive!Learn More
    + +