title: OData Source Component in SSIS Hanging
body: <p>I'm using SSIS and would like to pull in the public NADAC data from this page using the OData connection:  <a href="https://data.medicaid.gov/Drug-Pricing-and-Payment/NADAC-National-Average-Drug-Acquisition-Cost-/a4y5-998d" rel="nofollow noreferrer">https://data.medicaid.gov/Drug-Pricing-and-Payment/NADAC-National-Average-Drug-Acquisition-Cost-/a4y5-998d</a>.</p>
  
  <p>You can find the OData endpoint on their page by clicking on the ellipse and selecting <em>Access Data via OData</em>. The v4 endpoint is: <a href="https://data.medicaid.gov/api/odata/v4/a4y5-998d" rel="nofollow noreferrer">https://data.medicaid.gov/api/odata/v4/a4y5-998d</a></p>
  
  <p>In SSIS (Visual Studio 2015, Version 14.0.500.272 targeted at SQL Server 2016), I am able to create an OData Source Connection Manager to the base URL: <a href="https://data.medicaid.gov/api/odata/v4/" rel="nofollow noreferrer">https://data.medicaid.gov/api/odata/v4/</a></p>
  
  <p><a href="https://i.stack.imgur.com/ZQsYM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZQsYM.png" alt="Image of OData Connection Manager Editor with the URL clearly displayed, Windows Authentication selected, and a message &quot;Test connection succeeded&quot;"></a></p>
  
  <p>You can see the test connection succeeded. <em>Note: If I specify the full OData End Point URL here, it does succeed, but then doesn't find any collections to connect to in the next step, which I why I'm not doing that. (The message is "Unable to load collection for the connection OData Source, reason: Cannot open this connection")</em></p>
  
  <p>Also note, I'm using "Windows Authentication" but this is a public feed and no authentication is required. There doesn't appear to be a "anonymous authentication" option.</p>
  
  <p>The next step is to drag the OData Source onto my data flow and right-click to edit it. When I do this, I first select the OData connection manager I just created. It recognizes the connection and displays a drop-down list of collections I can connect to.</p>
  
  <p><a href="https://i.stack.imgur.com/DrnuM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DrnuM.png" alt="Picture of the OData Source Editor showing a drop-down list of collections"></a></p>
  
  <p>Clearly, I'm doing something right up until this point, since it's recognizing the collections that are available.</p>
  
  <p>However, whether I:</p>
  
  <ol>
  <li>Select any collection in the list</li>
  <li>Select the collection in the list that I want (a4y5-998d)</li>
  <li>Switch from collection to resource path and paste in the resource path I want (a4y5-998d)</li>
  </ol>
  
  <p>and then click "Preview..." or "Columns" or even "OK" - Visual Studio becomes entirely non-responsive. For 20+ minutes. I have to use Task Manager to crash out and try again. If I let it sit forever, I eventually get a yellow warning message: <em>Pipeline component has returned HRESULT error code 0xC020801F from a method call.</em></p>
  
  <p>I also haven't had any success with the v2 OData feed endpoint that Medicaid provides (<a href="https://data.medicaid.gov/OData.svc/a4y5-998d" rel="nofollow noreferrer">https://data.medicaid.gov/OData.svc/a4y5-998d</a>).</p>
  
  <p>My two questions:</p>
  
  <ol>
  <li>Am I doing something obviously wrong? Perhaps I'm using the wrong URL, or not setting something correctly?</li>
  <li>Do you experience the same problem or are you able to get it to work using the steps I've tried? To recreate, create a new SSIS package with a Data Flow component and follow my steps. </li>
  </ol>
  
  <p>Note: I am able to bring the data in using Power BI (for example), so I don't think there's anything wrong with the feed. It's just SSIS I'm struggling with.</p>
  
  <p>Thanks in advance for any help.</p>