What is SSIS "thinking"?


  • I inherited a pretty poorly developed stored proc. It's going to be refactored but it's still going to be complicated.  The  proc is going to be used as source data for a OLE DB Source in a data flow task. When open the Data flow source dialogue and tell it to EXEC usp_JunkyProc and close the dialogue box BIDS will just kind of lock up and sit there and think and think and think not really seem like it's doing anything. It will sit there for upwards of 15 minutes...and then it will be fine.

    What exactly is going on when you click ok after closing an OLE DB Source dialogue box?

    Wednesday, September 25, 2013 8:43 PM

All replies

  • It is doing a metadata discovery. Perhaps the code in the SP is tangled spaghetti. Consider cleaning it up.

    Also this is typically a sign of underpowered machine. To develop comfortably you need a pretty robust up-to-date computer.

    Arthur My Blog

    Wednesday, September 25, 2013 8:56 PM
  • Intel i3 2350M 2.3Ghz 8GB RAM. I don't normally have problems until I started dealing with this thing.

    The proc itself is actually just pulling 8 columns from a view. The view however is total junk. I'm sure you've seen this before where someone writes a view that selects from a view that selects from a view. It's Viewception.

    Any tips on how to prevent this long running metadata discovery or should I take this down to the T-SQL board?

    Wednesday, September 25, 2013 9:30 PM
  • Try setting BypassPrepare to false and it may alleviate the wait time involved.

    Arthur My Blog

    Thursday, September 26, 2013 2:41 PM
  • It takes so freaking long to open that I haven't even been able to test this yet. However, you're showing a screen shot from an Execute SQL task and my problem is with an OLE DB Source in the data flow task. BypassPrepare doesn't appear to be a property that I can edit.
    Thursday, September 26, 2013 8:20 PM
  • My bad, I think I wanted to say ValidateExternalMetadata needs to be set to false in the Advanced Editor:

    Arthur My Blog

    Thursday, September 26, 2013 8:33 PM
  • Well that didn't work either. :(
    Thursday, September 26, 2013 10:03 PM
  • Can we see what does it run in details please?

    Arthur My Blog

    Friday, September 27, 2013 9:13 PM
  • What do you mean? Like the code? It's freaking huge. I just mapped it out actually. There is the main view which is simplistic. That view calls three other views and joins them together in a result set. One of those views calls a 4th view. The view that calls three other views is super convoluted with different types of joins, derived tables, and a group by with 6 different conditions some of which include arithmetic.
    Friday, September 27, 2013 9:23 PM
  • The main view is what get's called from SSIS.

    [20 columns]
    FROM user_view
    WHERE [date_value] BETWEEN @StartDate AND @EndDate

    And then user_view is the super crazy one.

    Friday, September 27, 2013 9:27 PM
  • I see, try setting DelayValidation to TRUE

    Arthur My Blog

    Tuesday, October 01, 2013 3:15 PM
  • That was the very first thing I did. That's why I'm scratching my head. That should have fixed it.
    Tuesday, October 01, 2013 3:16 PM
  • Alas, we have exhausted all the options, and really the issue is with the underlined view complexity.

    I am afraid there is nothing else left to be done here.

    Arthur My Blog

    Tuesday, October 01, 2013 3:20 PM
  • So I'm getting ready to refactor it right? There are five views involved so it's still going to be crazy. I'm probably going to substitute the views for for temp tables so we're not going to be referencing a view from a view. It's still probably going to be pretty complex, it's just going to be all contained in one T-SQL script. Any tips on how to prevent the meta data issues I'm currently struggling with?
    Tuesday, October 01, 2013 3:44 PM
  • Yes, refactoring is the word, make it a stored procedure - they are more lean on the parser.

    Do not go with temp tables.

    Arthur My Blog

    Tuesday, October 01, 2013 3:53 PM
  • I  have no idea what the alternative would be. The reason the original developer went with views is because they are very complex and stand alone queries. The results of two of the views are back from the beginning of time and far to large to use a table variable. I suppose I could use a CTE but the performance gain over a temp table in this instance I'd forecast to be negligible.

    It already is a stored proc but it looks like this:

    stored proc calls -> view1 calls -> view2 calls -> view3

    In my planned version I'm going to take all those views and in the stored proc drop them into temp tables or CTEs or whatever you suggest as opposed to the way it is now where you have to open 4 scripts just to see what is going on. Make sense?

    Tuesday, October 01, 2013 4:01 PM