1

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!

9
  • What is the wait_type? You can use sys.dm_os_waiting_tasks to see that. Sep 8, 2017 at 16:09
  • high cpu sounds like excessive (table/index) scanning of in-memory pages and/or some sort of spinlock contention; generally speaking, find the query in question, obtain the query plan, and do some basic P&T; could be something as simple as a missing index or missing statistics (even on system/catalog tables)
    – markp-fuso
    Sep 8, 2017 at 16:10
  • @SqlWorldWide I don't see any session IDs that match the SPID I see on sp_who2... I thought session IDs mapped to SPIDs, but maybe they don't?
    – ARich
    Sep 8, 2017 at 16:22
  • They are same number. I suggest you use sp_whoisactive which shows everything in one place and lot easier. Also there are ways to collect wait_stats for one session. sqlskills.com/blogs/paul/… Sep 8, 2017 at 16:26
  • @markp how would I see if it's something on a system/catalog table?
    – ARich
    Sep 8, 2017 at 16:33

2 Answers 2

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

1
  • 1
    Thanks for the answer. Correct me if I'm wrong, but the proposed solution appears to be another workaround...
    – ARich
    Oct 4, 2017 at 14:31
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.