March 14, 2008 at 11:38 am
does anyone know how to configure VS so it will not validate a DTSX package when loading it.
This is causing us some problems where the validate on load will hang the app due to a changed data connection on a remote server. - there's got to be a way to stop it.
thanks
March 15, 2008 at 3:32 am
I've always been wondering why this question isn't raised more often. Some of my projects take more than an hour to open. I usually get around this problem by modifying the connections strings in the .dtsx package files with a plain text editor. But if you have a lot of packages it may take a lot of time as well.
Peter
March 17, 2008 at 8:26 am
Peter, thanks for the acknowledgement on this, thought it was just me. Anyone have a suggestion on this?
- tom
March 18, 2008 at 2:57 am
No, no suggestion but the same 'problem'.
Any pointers on this would be most appreciated.
HansLindgren
March 18, 2008 at 6:23 am
there is package property named Delay Validation that is set to False by default. Try setting it to True and see if that helps. It delays package validation until execution when set to True. I don't have any large packages to test it on.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
March 18, 2008 at 7:50 am
Yes, setting the delay validation to true will work. Not only on large packages but also for data readers to certain types of sources.
March 18, 2008 at 8:04 am
Thanx! I will test it as soon as I encounter these packages again.
Hanslindgren
October 10, 2008 at 5:09 pm
After opening the solution/project, before opening any package, set the VS to offline mode (SSIS -> Work Offline). Then try opening the packages. I feel that the packages open up much quicker.
October 11, 2008 at 2:42 pm
I will give offline thing a try on monday.
Also the delay validation only appears to affect the runtime validation. I didn't notice any difference.
My work around to the slow loading was to break the package into smaller dtsx packages. Then I could work on part without having to load the whole thing. Also when scheduing a job each step in the job calls the next package.
Lastly there is an execute package task that you could use to execute all the smaller packages as one, but you will not have access to variables in the master package. A work around for that would be to persist the variables in a table or set up a sql config for the variables. At package end, update the configs of the next package being callled then those values will be available when the next package loads.
December 11, 2008 at 10:56 pm
Allow the user to stop validation by hitting CTLR+BREAK.
This would stop validation and maybe put SSIS into offline mode. When someone then needs to validate they can go back online.
October 6, 2009 at 6:22 am
Hi guy’s
I recently had the same problem! I know that it loads quicker when you “Work Offline” (SSIS menu) or set the delay validation to True. But what I discovered was if you turnoff your DTC (Distributed Transaction Coordinator) Service off, it works like a charm.
October 6, 2009 at 9:38 pm
Thanks for the tip on the DTC, I'll have to check into that.
just to clarify the delay validation has nothing to do with loading a package into VS. This is a runtime setting that will delay the validation of a component until just before it executes.
We've not had any problems with package loading since we've trimmed down the size that a dtsx package can be. In the early days we would try to build very large and complex projects within a single dtsx file. Now the standard is to break out the project into small manageable pieces (dtsx packages)
Generally we will have at least 4 packages per project, these are Data Extract, Data Quality checks, Transformations/Staging, and Final insert / update to production.
As projects grow larger we may break these steps down into multiple packages as well. This is a good practice to follow as it speeds development and allow separate BI programmers to work on pieces of the project since merging a DTSX file is not feasible. Speeds up troubleshooting, and testing as well.
tom
December 10, 2009 at 12:23 am
Problem solved!
First of all, what I tried is to change all the
"DelayValidation">0 to "DelayValidation">-1, which didn't work, my package still just sat and did nothing.
Then I went to check in the sql database on the user id which is used to run the SSIS package, to my surprise, I found the following statement running
SELECT * FROM FILE089.
First of all, I was busy with an update on this table,
And Secondly, this file have about 54 million records in at that stage.
I killed the process, and what do you know, the SSIS package loaded further with out any further problems.
I assume its because I had a lock on the table that the SSIS package did validate any further, and I really hope that its not because of the SSIS package really trying to retrieve 54 mil records.
So, solution, if you are importing / exporting data into your SQL database, kill the process which tries to retrieve all the records.
December 10, 2009 at 2:38 am
chris.stuart (12/10/2009)
Then I went to check in the sql database on the user id which is used to run the SSIS package, to my surprise, I found the following statement runningSELECT * FROM FILE089.
(...)
And Secondly, this file have about 54 million records in at that stage.
Chris,
I'd check the settings of 'SET FMTONLY' for that connection.
I believe it was not doing a full select but was only trying to retrieve the structure of the table (for validation purposes)
BOL on 'SET FMTONLY':
Returns only metadata to the client. Can be used to test the format of the response without actually running the query.
No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.
The setting of SET FMTONLY is set at execute or run time and not at parse time.
Regards,
Hanslindgren
December 10, 2009 at 2:50 am
Hi Hans
Had a quick look but it seems to be only a setting in SQL Server and not in SSIS it self or is there somewhere in SSIS where I can control it?
Thanks
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply