April 21, 2016 at 9:26 am
Can I get some advice on deploying SSIS/SSRS solutions (SQL Server 2014) from Visual Studio?
If memory serves, I think this was installed from SSDTBI_VS2012_x86_ENU.exe. (Data Tools exe)
Situation is I am developing solutions from my desktop using Visual Studio 2012 Shell (Integrated). The solutions are stored and deployed to an integration services server running SQL Server 2014. They run completely fine and can also be called from SQL Server Agent via a job. What I'd like to do is to be able to modify and re-deploy the solutions directly on the server (especially useful when remote desktop-ing). On the server I already have a couple of programs installed - namely "SQL Server Data Tools for Visual Studio 2013" and "Visual Studio Professional 2012". I open the former one, which shows as "Visual Studio 2013 Integrated (Shell)" when opening, and when I select my existing 2012 package I am prompted with "Welcome to SSIS Package Upgrade Wizard". In the Select Package menu I select my package (called "FleetList.dtsx") and on next Menu, Select Package Management Options, I leave the default selections ticked ("Update Connection strings to use new provider names", "Continue upgrade process when a package upgrade fails", "Ignore Configuration") and the others left unticked ("Validate Upgraded packages", "Create new Package ID"). On clicking "Next" to perform Upgrade I get 3 errors: (1) "The package format was migrated from version 6 to version 8. It must be saved to retain migration changes.", (2) "The connection "{DD029D7E-E3CO-41CO-BFD7-................." is not found. This error is thrown by Connections collection when the specific connection element is not found.", (3) "Error: Package Upgrade: The loading of the package FleetList.dtsx has failed".
I then have a message stating "The package is upgraded. See list of warnings ..."
I click "ok" and the package opens with some Warning messages. However, when I right-click and execute package I immediately have a "Save File As" window appear inviting me to save the solution. I select a location within the current solution folder (defaults to this), but I get an Access Denied message "System.UnauthorisedAccessException: Access to the path 'E:\Finance\Server\............. \Development\Project.params is denied".
The other option I was contemplating was to uninstall all Visual Studio versions from server and try to do VS 2012 Integrated Shell install from scratch.
I have tried this already without removing existing versions but of course I get a message stating components already installed.
Can anyone help with the correct procedure with regards to deploying these existing project solutions to the server whether with the same VS version or upgraded version?
April 21, 2016 at 10:51 am
There's a lot going on here and I understand your confusion. There are several moving parts. I'll do my best to clarify some things – if anyone sees any errors in what I have written, please chime in.
SSIS package file formats changed significantly between SSDT 2012 and SSDT 2013. Once you have upgraded a package to 2013, you can't go back, unless you have a backup copy.
The upgrade process changes a package's 'PackageFormatVersion' property to 8.
On SQL Server 2012, the PackageFormatVersion needs to be 6 for packages to run.
On SQL Server 2014, the default PackageFormatVersion is 8.
That's all fairly straightforward.
But now add SSMS into the equation. It sometimes tries too hard to be helpful.
If you build an ispac in 2012 and try to deploy it to a 2012 server using SSMS 2014, SSMS helpfully changes the PackageFormatVersion to 8 during deployment. Try to run one of those deployed packages and you'll get the error you got.
I haven't tried all of the possible combinations of SSDT, SSMS and SQL Server to investigate what works, because that would be utterly boring. But if you stick to one of the following combinations, everything should work fine:
1) SSDT-2012 / SSMS 2012 / SQL Server 2012
2) SSDT-2013 / SSMS 2014 / SQL Server 2014
3) VS 2015 / SSMS 2016 / SQL Server 2016
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 8:34 am
Hi Phil,
Thanks for info but I'll try to just elaborate on my specific issue.
I deploy from Visual Studio Integrated Shell 2012 to server running SSMS 2014.
My configuration is - Desktop app running VS Int Shell 2012 - this is what creates the project and I deploy to a SSISDB catalogue on server which is running SS2014.
Problem is I have been through a few versions of SQL and VS on desktop and finally got VS2012 working although I have to ensure .Net 4.5 updates are prevented from installing. So I'm developing locally on VS2012 and deploying to server. If I try to open solutions from server using the VS package installed there (VS 2013 Integrated Shell) then I'm prompted to upgrade and then I run into issues.
So these projects are version 6 and run successfully on SS2014.
I can run them from SSMS Agent on 2014 via a SSIS Proxy.
The problem I have is in installing the right version of VS on server so that this is compatible with the existing locally developed solutions, and then I can edit them on the server by opening VS on the server rather than on local machine.
April 22, 2016 at 8:57 am
DuncEduardo (4/22/2016)
Hi Phil,Thanks for info but I'll try to just elaborate on my specific issue.
I deploy from Visual Studio Integrated Shell 2012 to server running SSMS 2014.
My configuration is - Desktop app running VS Int Shell 2012 - this is what creates the project and I deploy to a SSISDB catalogue on server which is running SS2014.
Problem is I have been through a few versions of SQL and VS on desktop and finally got VS2012 working although I have to ensure .Net 4.5 updates are prevented from installing. So I'm developing locally on VS2012 and deploying to server. If I try to open solutions from server using the VS package installed there (VS 2013 Integrated Shell) then I'm prompted to upgrade and then I run into issues.
So these projects are version 6 and run successfully on SS2014.
I can run them from SSMS Agent on 2014 via a SSIS Proxy.
The problem I have is in installing the right version of VS on server so that this is compatible with the existing locally developed solutions, and then I can edit them on the server by opening VS on the server rather than on local machine.
Bottom line is that you won't get this resolved while you insist on trying to use both SSDT 2012 and 2013.
I advise you to take the leap and move to 2013, given your use of the 2014 DB engine.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 9:02 am
Yeah but does that mean I have to recreate the packages on 2013 from scratch? At the moment I'm unable to upgrade.
April 22, 2016 at 9:27 am
DuncEduardo (4/22/2016)
Yeah but does that mean I have to recreate the packages on 2013 from scratch? At the moment I'm unable to upgrade.
No, the upgrade process should be as simple as opening the project in SSDT 2013 and allowing the upgrade to happen.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 9:40 am
So if you read again my first post I detail exactly what happens when I try to upgrade. This is what I'm trying to resolve, and hoping to get advice on, as maybe I am missing something
April 22, 2016 at 9:44 am
DuncEduardo (4/22/2016)
So if you read again my first post I detail exactly what happens when I try to upgrade. This is what I'm trying to resolve, and hoping to get advice on, as maybe I am missing something
On a test copy of the project, open in SSDT 2013 and upgrade everything. Accept the warnings and errors & save if you can.
Then go through the errors methodically & try to understand why they are occurring. You should find that they are easy to resolve.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 9:45 am
Phil Parkin (4/22/2016)
DuncEduardo (4/22/2016)
So if you read again my first post I detail exactly what happens when I try to upgrade. This is what I'm trying to resolve, and hoping to get advice on, as maybe I am missing somethingOn a test copy of the project, open in SSDT 2013 and upgrade everything. Accept the warnings and errors & save if you can.
Then go through the errors methodically & try to understand why they are occurring. You should find that they are easy to resolve.
When you do this, make sure that you are in an environment where the existing connections validate properly (ie, the connections all 'work').
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2016 at 7:06 am
Phil,
When I start Visual Studio 2013 Shell (Integrated), open project and I upgrade it says Upgrade Complete, albeit with several warnings and 2 errors. Main error is "invalid connection" (the reference is a long hex string). Even if I uncheck the upgrade selection the process still seems to try to validate connections. However, when I try to use the package it immediately prompts me to Save File As (save package) and then I get an Access Denied message. So, I am just trying to execute the package in order to see the errors but I can't get to this point due to this problem. I am opening the project from the current location and trying to save back to that location so don't understand the access denied error.
April 25, 2016 at 7:45 am
DuncEduardo (4/25/2016)
Phil,When I start Visual Studio 2013 Shell (Integrated), open project and I upgrade it says Upgrade Complete, albeit with several warnings and 2 errors. Main error is "invalid connection" (the reference is a long hex string). Even if I uncheck the upgrade selection the process still seems to try to validate connections. However, when I try to use the package it immediately prompts me to Save File As (save package) and then I get an Access Denied message. So, I am just trying to execute the package in order to see the errors but I can't get to this point due to this problem. I am opening the project from the current location and trying to save back to that location so don't understand the access denied error.
Yes, that's a puzzle. Try saving it somewhere else (after verifying that you can create, modify and delete files there) & see whether the same happens. Maybe this is just a permissions issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2016 at 9:18 am
Phil, it turns out that if I run Visual Studio Pro 2012 on server "as admin" then I'm able to save and execute package (fixed minor errors).
In addition I deleted the .ispac file prior to this.
Also, again running "as admin", I'm able to run "Visual Studio 2013 Integrated (Shell)" on server and run through the upgrade process, and save resultant upgraded project, and execute the 2013 solution. The existing SQL Server Agent job (2014), after deploying project to SSISDB catalogue, picks up upgraded dtsx fine and executes fine.
So, it was down to permissions really.
It's still strange though as I was an administrator on the server.
April 25, 2016 at 9:27 am
DuncEduardo (4/25/2016)
Phil, it turns out that if I run Visual Studio Pro 2012 on server "as admin" then I'm able to save and execute package (fixed minor errors).In addition I deleted the .ispac file prior to this.
Also, again running "as admin", I'm able to run "Visual Studio 2013 Integrated (Shell)" on server and run through the upgrade process, and save resultant upgraded project, and execute the 2013 solution. The existing SQL Server Agent job (2014), after deploying project to SSISDB catalogue, picks up upgraded dtsx fine and executes fine.
So, it was down to permissions really.
It's still strange though as I was an administrator on the server.
It's been a bit of a saga, but you got there in the end, which is good news 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2016 at 9:29 am
and thanks for taking the time to impart your valuable advice.
P.S. just posted another which you may find interesting - that old nugget of loading Excel to SQL
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply