I have a query I'm using as the source for an OLE DB source component. When I load the package or try to execute the package, the validation on that component takes a very long time. In fact, I left the package overnight and it hadn't finished validating the next morning.
I've found so many (too many) examples of how to delay validation, work offline, etc., but I don't want to workaround the validation. I want to figure out how to make the validation process faster. Currently, when I run the query in SSMS it returns one row (total) in 20 seconds, so why does validation currently run for many hours without completion?
When I run
sp_who2 I see that package is executing a select statement against the db with very high
CPUTime and low
DiskIO (e.g. 390000/300). Is this the issue? If so, how do I correct this or where do need to go to investigate how to fix this? My package is sitting on the same server that the SQL Serve instance is running on, so I can't imagine connection issues are the cause of the validation slowness.
I'm on a tight deadline, so I will have to stop investigating the root cause of this for now. To workaround this, I replaced the Data Flow task where the OLE DB Source component was using this query with an Execute SQL task. I then created a stored procedure that simply does
INSERT INTO TableName SELECT Col1,Col2,ect. FROM ViewName. When I run the package now it all runs in a reasonable amount of time like I would expect it to. I'm not going to make this the answer to the question because this still really bothers me, and I'm losing some built in logging functionality in SSIS to do it this way. If anyone knows the asnwer, please let me know!