1

In SSIS I have the simple query OLE db source pulling from Azure SQL db and passing a date to a WHERE clause as:

enter image description here

with parameter:

enter image description here

It is at this step during execution that is hangs for 5+ min on the Pre-Execute phase is beginning, even if the datetime value being passed would return ~100 records.

However, if I hardcode the date to say '2019-09-01' it will execute in seconds...

How can I change things to make this run in a reasonable amount of time?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Sauron
  • 6,399
  • 14
  • 71
  • 136
  • 1
    The "hanging on pre-execute' issue is a dreaded one that I'm not sure how to solve. But it would help if you explained how you are running the package - in SSDT? In Azure on-premise? In Azure Data Factory? – Nick.Mc Sep 23 '19 at 22:55
  • Not sure which environment you are running your task but I would check run-time variable value by debugging in SSDT to make sure it's passing the correct date format.. Just a thought!! – Mboolean Sep 24 '19 at 14:51
  • @Nick.McDermaid What it ended up being, was SSIS was not using the index on the datestamp column, we added a query hint to force the usage of that specific index and now it flies – Sauron Sep 25 '19 at 17:22

1 Answers1

1

Since OLE DB source may have some issues detecting the parameter data ype, try adding a cast operation to the parameter:

Where [mnDateTimeStamp] >= CAST(? AS Date)

Another Workaround

If it still not working, you can create a variable of type String. And define an expression to evaluate this variable. As example:

"SELECT * FROM Table Where [mnDateTimeStamp] >= '" + (DT_WSTR,50)@[User::LastRunDateTime] + "'"

Then from the OLE DB Source, use SQL Command from Variable option and select that variable.

Hadi
  • 36,233
  • 13
  • 65
  • 124