Wednesday, March 25, 2015

SSIS 2012 package with Odata source–execution failed (part 3)

In post SSIS 2012 package with Odata source-failure (part 2) I was able to deploy my package with Odata source. But the package failed to run.

Change from Project deployment to Package Deployment

I have converted my project with package connections from project deployment to Package deployment. Then I deployed the package on the server in MSDB catalog from SSIS. I.e. I connect with SSMS to Integration Services as an administrator and under the MSDB folder I added a new folder for my package. I then imported the dtsx into the mdsb package store. The Import worked. Still from within SSMS and connected to the Integration Services, i right-clicked on the package in the MSDB store and selected 'Run package'. In the Execute Package dialog window, I clicked on 'Execute' and the package actually ran. The Odata connector was opened, it read the data and imported into my database.

This experience made me realize what the real culprit  was: The OData connector is a 32-bit connector in SQL Server 2012. 

Odata source is 32-bit

To prove this: with SSMS I connected to the SQL database server and in the Integration services catalog, i right-clicked on the package that was deployed in Project deployment mode, then selected 'Execute...'. In the execute dialog windo in the 'Advanced' tab I checked 32-bit runtime. And the package ran.

Final conclusion

  1. The Project deployment will work, but not with the Odata source as project connection. The Odata connection must be defined in each package as a package connection.
  2. Run the deployed packages in 32-bit mode on the server

1 comment:

Jennifer Volkening said...

Thank-you for taking the time to post your experience and findings about this issue. I was stumped at the point described in part (3) of your blog and your explanation of how to handle the connection type not found issue was exactly what I needed in order to get the packages to work on the server after they were deployed.