SSIS package deploy to remote SQL Server 2012

1
votes

I have created SSIS packages on

VS 2010 shell

and I am able to successfully deploy my packages to SSISDB/Folder/Projects/PackageName (local server)

But I am having trouble to deploy the same to remote SQL Server 2012.

How can I do that?

And where is my File system/ MS SQL Server package deployment in VS 2010 Shell?

I tried this: FTP the Package (ispac) to remote database server and run it from there. when i try that, I get the bellow error

Warning: Failed to decrypt an encrypted XML node. Verify that the project was created by the same user. Project load will attempt to continue without the encrypted information.

Warning: Failed to decrypt sensitive data in project with a user key. You may not be the user who encrypted this project, or you are not using the same machine that was used to save the project. If the sensitive data is a parameter value, the value may be required to run the package on the Integration Services server.
1
visual-studio-2010deploymentssispackage

1 Answers

4
votes

So ... there's a lot to unpack in your question.

In SQL Server 2012, Microsoft introduced a new default deployment model, the Project Deployment Model. Instead of deploying individual .DTSX package files you "build" a project deployment file (your .ISPAC) and deploy that in toto to the server.

So that's why you don't see File system/package deployment model. You can if you want change a project's deployment model to Package Deployment Model. Just right-click on the Project and select "Convert to Package Deployment Model" and you can then proceed to deploy your packages individually to the File System or MSDB Store as before.

(I don't recommend this by the way, the project deployment is pretty sweet.)

So one question is what error do you see when you go through the Deploy process of VS2010 for your SSIS Project and choose the remote server as your target for deployment?

For your encryption issue, read this link about Access and Security Controls in SSIS Packages, and pay particular attention to the section at the bottom entitled "Protection Level Setting Based on Package Life Cycle" which has some best practices for modifying package encryption levels when migrating packages from a development environment to a production environment.