SQL 2012 SSIS Single Package Deployment
I have the following scenario: I have a 2012 SSIS project that contains two packages and on the release of the project, I deploy the entire project solution which has both packages. I then create a SQL Agent job that executes the packages from the SSIS catalog and all is well. Down the road, a change is required for both packages and both packages are being developed. The first package change is ready to be deployed and the business wants the change to be implemented and does not want to wait for the second package development. How do I deploy my project to get the first package deployed without overwriting the second package? I do not want to drop out the second package from the project because the agent job is depending on the package to be located in the catalog. However, I cannot just create a new project deployment with the second package changes since the second package is not ready to be deployed yet. Is this possible? In the deployment wizard, nothing is available to select only a specific package to update. I want to take advantage of the SSIS catalog for configuring connections, parameters and the like but this ability to replace a single package needs to be available. Can we do something in Powershell to add the updated package to the packages collection, which will overwrite the existing package or would that mess up the project deployment and the OOTB versioning that exists? Or, is the workaround to create a new project with the Package One Updates and the original Package Two, walk through the deployment wizard to create the ISPAC file and then deploy that project to the catalog. (I guess answering my own question here but really hoping for something more elegant) Thanks in advance Chuck
April 27th, 2012 11:54am
Have you ruled out manual deployment of the package (no wizards)? This is the mode I deploy most packages - either manaully copy them to the file directory that they will run from or use the import functionality in SSMS if they will reside in the DB. Can't say that I've ever used the "Deploy" from VS on anything but dev environments.Chuck
April 27th, 2012 12:05pm
for SQL 2012, it does not appear that you can add the package to the catalog. right clicking on the catalog project only allows for configuring the environment, I do not see an option to add a package to an existing project. for previous versions of SQL Server, I used the file directory all of the time and created the jobs from the directory taking advantage of configurations for the connection strings and so forth. for 2012, you can still do the individual packages as before and create a job from that but i want to take advantage of the new catalog features, which is the question. how can i deploy to the 2012 catalog with just updating a single package in the project solution short of creating a separate deployment project?
April 27th, 2012 12:50pm
OK, I see what you mean - I was still thinking about the old model of deploying into the msdb (even in 2012). I messed with it a bit and couldn't figure out any direct way to either deploy a single package or a way to strip it out of the deployment package.Chuck
April 27th, 2012 2:18pm
SSMS-->Integration Service Catalogs-->SSISDB-->Your project-->projects right click-->import pakcages, this might be the one of walkarounds. but i do not think it is a sound solution. it should enable the single package deployment from SSDT.Derek
May 14th, 2012 11:22am
SSMS-->Integration Service Catalogs-->SSISDB-->Your project-->projects right click-->import pakcages, this might be the one of walkarounds. but i do not think it is a sound solution. it should enable the single package deployment from SSDT. Derek I'm not convinced this approach helps matters at all. I've tried importing a single package to an existing project but it overwrites the project. Does anyone know of a way around this or if I'm doing something stupid?
May 16th, 2012 7:34am
unfortunately, if you do the import packages as mentioned above, it allows you to select package files that you want to import but then it creates an entirely new project deployment file and overwrites whatever is on the server as opposed to just adding a package to the project in the catalog. i could see the package with the same name being overwritten in the catalog but to actually overwrite the entire project and drop out existing packages from the catalog project does not make sense to me.
May 17th, 2012 8:27am
One more note. Now if we look at coding with the assembly Microsoft.SqlServer.Management.IntegrationServices The PackageCollection object does support an .Add and .Remove method - both come with warnings not to use in code that they support internal SQL processes. If you use them they seem to only deal with the collection class list and have no impact on the database. Chuck
May 17th, 2012 9:08am
well, if the packages are being extracted from the object data pertaining to the version of the project itself, then you are correct, there does not appear to be a work around aside from a procedural method. The procedural method I see then is to do the following: Navigate to the Catalog and do an Export on the Project to create an ISPAC fileGo to Data Tools and create a New Project selecting the Integration Services Import Project WizardSelect the exported ISPAC file as the source for the new project in the wizard which will then open in SQL Data Tools with all of the packages that were in the catalog for that projectIn the opened project, add the package with the update that you are looking to deploy, keep the other packages as isDeploy the updated project back to the catalog by the same deployment steps. Not exactly a clean process, source control is going to be huge. I feel that this may lead for those who are maintaining their nightly jobs that use SSIS, they are going to keep creating their SSIS tasks the same way as in 2005 and R2, via File locations and not taken advantage of the catalog.
May 17th, 2012 10:29am
It may just encourage people to keep their projects small - relatively few related packages per project. If I'm deploying a change to one package that populates part of a DW I don't really have any problem with redeploying all 20 packages since they would have had to be unit tested in QA as a group anyways.Chuck
May 17th, 2012 10:33am
This is an older thread, but it was recently linked on my blog post about this topic so I thought I would comment. Short answer is no, you can't update a single package in the project. You always deploy the entire project to the Catalog. The idea being that the packages make up a larger "Data Integration Solution", and should be kept together within the project. If the packages are unrelated, consider creating separate projects for them.
July 13th, 2012 11:20am
That is correct - the Project file is kept within the Catalog. When the package is executed, the Project file is passed to ISServerExec (conceptually at least). When you rollback to another project version, it's basically updating the pointer to the current version of the project file.
July 13th, 2012 11:21am
One more post on this thread :) You can skip #1 in this case... the Integration Services Import Project Wizard can import projects directly from an SSIS Catalog.
July 13th, 2012 11:23am
I am unable to understand then how is Project Deployment Model different from Package Deployment Model when there is no way we can deploy a single package at all??? This is so confusing!!
August 22nd, 2012 8:40pm
I am unable to understand then how is Project Deployment Model different from Package Deployment Model when there is no way we can deploy a single package at all??? This is so confusing!! Project deployment model (since 2012): you deploy the packages in a project all at once. There is a real concept of project. Package deployment model (since 2005): you deploy packages individually. There's no concept of project, with the exception of the Visual Studio Project, which has nothing to do with SSIS per se.MCTS, MCITP - Please mark posts as answered where appropriate.
August 23rd, 2012 1:49am