How do I shorten validation time?

Question

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.

UPDATE

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!

Answer 1

I think that your OLE DB source is causing the issue

1. Create a new table with the same schema as your current source and point the component at it.
2. Within the package, update to your actual source, as this will force it to handle it at run-time. The easiest way I have found to do this is to create a variable and assign it to the source expression. See an example of this at https://stackoverflow.com/questions/18288183/parameterized-oledb-source-query

Another possible solution is to set the DelayValidation property of the Data Flow task to True - See https://technet.microsoft.com/en-us/library/aa337088(v=sql.105).aspx#Anchor_4

Answer 2

I had the same issue too, finally this solution below help:

1. Go to Extensions > SSIS > select 'Work Offline'
2. SET 'Delay Validation=TRUE' for all variable connections.
3. Right click OLE DB Source or OLE DB Destination > Show Advanced Editor > Component Tab > Validate External Data set to False
4. uncheck 'Work Offline' then run the package again