September 9, 2019 at 12:55 pm
I am using visual studio 2013 to develop and maintain our SSIS packages, we deploy the packages to the package store on SQL server 2014 using the package deployment model. We have a new requirement to bring in data from an azure SQL database, this isn't working when I build a package in visual studio 2013, however, it does work from visual studio 2015 when I use authentication=ActiveDirectoryIntegrated. So I decided to create a new BI project using visual studio 2015, for only the new packages connecting to an azure SQL database with the aim of deploying the new packages to the same location as our current packages.
Once deployed, I right clicked and executed the new package, although it did return error message "To run an SSIS package outside SSDT you must install SSIS log provider of integration services or higher", it did load all of the data into my table. However, when I run it as part of SQL Agent using the service account, it doesn't run and I get an error message "ADO NET connection manager failed to acquire connection"
What is even more strange is that since deploying my new package that was built in visual studio 2015, none of my existing packages will run. They are returning the same error above "To run an SSIS package outside SSDT you must install SSIS log provider of integration services or higher". Also, logging isn't working, nothing is being written to our SSISConfigurations table.
I was under the impression that I could develop packages with both visual studio 2013 and 2015 and deploy them to the same place, but I'm guessing this isn't correct?
September 9, 2019 at 4:45 pm
in order to run SSIS packages targeting a particular version of SSIS you do need to have that version installed on the server (or a higher version in some cases but handle with care) - so SSIS Integration Services for SQL Server 2014 can run a Package created for both 2008 R2 and for SQL 2012 and SQL 2014 - but SSIS for SQL 2012 will not run the package for SQL 2014.
And it is not enough to install SSDT on the server - that is what will give you the error you are seeing as it will only allow you to run the packages from within VS. Not only that but you should not install a Development tool on a Server.
Install SQL Server Integrations Services 2016 if your VS is 2015 and you selected the default target for 2015 (SQL 2016)
Install SQL Server Integrations Services 2014 if your VS is 2013 and you selected the default target for 2014 (SQL 2014)
Install SQL Server Integrations Services 2012 if your VS is 2012 and you selected the default target for 2013 (SQL 2012)
Note also that you need to have licensing for the higher version. (or all depending on the version you have (enterprise/standard) and if you have SA or not.
September 12, 2019 at 8:44 am
Thank you for your reply.
I am sorry but I missed out something important from my question. In order to get SSIS connected to the azure SQL database, we had to upgrade the .Net framework. I believe this may be what is causing the errors as it is now trying to use a version of SSIS that is not installed.
I am still investigating this and will post any updates here.
September 16, 2019 at 3:17 pm
A stab in the dark, but this sounds similar to an issue I had using the Project Deployment Model. A new server had been created installed with SQL 16, however whoever built the server installed SSMS 17. This meant there was 2 versions of Integration Services installed and any packages deployed using a ISPAC file would fail with similar messages to yours. Under this scenario we then had to deploy ISPAC files using a script file to ensure the correct ISDeploymentWizard.exe is used:
"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe" /Silent /ModelType:Project /SourcePath:"C:\Temp\MyProject.ispac" /DestinationServer:"MyServer" /DestinationPath:"/SSISDB/MyProject/Sales"
September 17, 2019 at 10:33 am
All, thank you for taking the time to reply. I really appreciate it.
When I log onto the same machine the package is deployed to it appears to run a bit further before hitting a different error message, the error message I am getting now is 'ADO NET Source has failed to acquire the connection (...) One or more errors occurred'.
Just as a reminder, I am trying to connect to an azure SQL database, I have installed the Azure feature pack for SSIS and updated the .Net framework, none of which has helped.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply