PRESS RELEASE: INTERFACE ACQUIRES CENTRIQ'S CORPORATE
IT TRAINING BUSINESS - WE ARE EXPANDING!   (Jan 5, 2023)
Interface and Centriq Logos
LEARN MORE
1-800-264-9029|602-266-8500
  • Course Schedule
    • Microsoft Training
      • Microsoft Fundamentals Training
      • Windows Client
      • Teams
      • Teams / Microsoft 365 / MS365
      • Windows Server 2019
      • SQL Server Transact-SQL
      • SQL Server Administration
      • SQL Server Business Intelligence
      • Power BI
      • Power Platform
      • Azure
      • Data Engineering / Machine Learning
      • Artificial Intelligence (AI)
      • M365 Messaging
      • PowerShell
      • System Center
      • SharePoint
      • Microsoft Security
    • Cloud Computing
      • Azure
      • Amazon Cloud (AWS)
    • Developer Training
      • DevOps
      • Web Development
      • .NET Development and Visual Studio
      • Java Programming
      • Python
    • Cisco Training
      • Cisco CCNA
      • Cisco Routing and Switching
      • Cisco Security
      • Cisco Wireless
      • Cisco Data Center and Storage
      • Cisco Collaboration
      • Cisco Contact Center
      • Cisco DevNet
    • CompTIA Certification
      • CompTIA A+
      • CompTIA CASP+
      • CompTIA Linux+
      • CompTIA Network+
      • CompTIA Project+
      • CompTIA Security+
    • Business Training
      • Project Management
      • ITIL
      • NIST
      • Business Analysis (PBA & CBAP)
      • Agile
    • Security Training
      • ISC2
      • Microsoft Security
      • CompTIA Security
      • NIST
      • Cisco Security
    • Wireless Training
      • Wireless Networking
    • Other Training
      • VMware
      • NetApp
      • F5 Networks
      • Citrix
      • Microsoft Excel
  • Exams
    • Student Pricing
  • Subscriptions
  • Interface Gold
    • RemoteLive™
    • Replay™
    • Interface Gold™ Benefits
  • On-Demand
  • Resources
    • Tech Blogs
    • Tech Videos
    • Microsoft Learn Catalog
  • About
    • Instructors
    • Interface TechPak
    • 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

  • On the Topic of Mastery
    On the Topic of Mastery
  • Understanding Network Cable Testers
    Understanding Network Cable Testers
  • Using Command Line Utilities for Troubleshooting Name Resolution
    Using Command Line Utilities for Troubleshooting Name…
  • The Difference between PMI-PBA Business Analysis Certification and IIBA CBAP Certification
    The Difference between PMI-PBA Business Analysis…
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

A Simple Introduction to Cisco CML2

A Simple Introduction to Cisco CML2

0 3622 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Cable Testers and How to Use them in Network Environments

Cable Testers and How to Use them in Network Environments

0 645 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 245 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

See what people are saying...

    Share your thoughts...

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

    Microsoft Subscription

    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 (128)
    • 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

    • The Easy Way to Convert Decimal Numbers to Binary Numbers and Back Again
    • Cisco CCNA and CCNP Certification Update 2019 – 2020
    • Using Command Line Utilities for Troubleshooting Name Resolution
    • What’s Up With The New Cisco Certification 2019 Program?
    • Concepts of Routing
    • Understanding Network Cable Testers

    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
    • Interface TechPak
    • 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 © 2023 Interface Technical Training. All Rights Reserved.

    Interface Live Training Terms and Conditions Terms of Use Microsoft Subscription Terms and Conditions Privacy Policy WIOA Policy

    Insert/edit link

    Enter the destination URL

    Or link to existing content

      No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.