I have SSIS Projects taking a long time to open with packages with a large number of data flows. Is there a way to turn off validation of metadata when a package opens Turn off validation during execution on SSIS Service (after previously validated in dev) Or be able to control when validation takes place in general
In my one package (1 of 5) I have 43 data flows (with a single source to target mapping) in 4 sequence containers, and it takes approximately 2-3 seconds per source to target mapping and sequence container to validate which will translate to 1 to 2 minutes to open. When the project with all 100+ tables for the data warehouse goes through validation, I can make coffee in the time it takes to open the project. I have to delete *.suo file (or verify all packages are closed in the designer and save the project file), and when I open the project, I have to jump immediately to SSISaWork Offline to set it to not validate the metadata to be able to work in a timely fashion. DelayValidation=TRUE does not help much.
Running in debug mode, has an effect of causing packages that were not open and validated to go through validation though I am not running those packages. Validate once during design and run forever.
Even if I re-open a package that I just closed from designer and had gone through validation, it will go through the validation process again.
It would be great if there could be an on-demand option off the menu bar to allow one to control when validation can take place for a project, or a more granular validation option for a specific data flow or container.
SSIS Package Validation taking a long time
trivun
During design time: you can't turn off the validation, but you can make it much faster if you switch to Offline Mode (in SSIS menu) to avoid connecting to external data sources (where most of the time is spent). Only basic (local) validation is performed, which is usually very fast.
You can't turn off the validation during execution time.
Pete Orologas
It would be nice to actually tell the user that the package is being validated. I raised a suggestion for this,
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx feedbackid=aeedf07f-ebd6-43b9-9f49-34c43d1a09b8
master_rigel2
Thanks Michael,
I have had to set the project to Work Offline to be able allow it to minimize the time for validation. I understand that some level of validation has to occur. It would be great to be able to specific Work Offline before one opens the project, and if set the project is set to Work Offline and opened, have a option off the menu bar to allow one to explicitly invoke the processing to validate the external metadata.
Another reason is at times I get an error message from BIDS during validation of the external metadata when a project is being open. It states that the application interface that was marshalled for a different thread (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)). I can't supply the screen print of the error message here to show you.