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
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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?
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 7:34am

Well, if you dont think this is one of approaches. DTutil might be the other you can try.Derek
May 16th, 2012 11:33am

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.
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 8:29am

I don't think it is going to be possible using any supported means. I've been looking around in the SSISDB tables and it appears that packages are always tied to a version of a project. Each time a project is deployed it creates new records in the internal.packages table that carry the new project version number. Now if you want to talk unsupported, that looks like it would get ugly if it is even possible. Consider the following query which is run in SSISDB. The object_data is all stored at the project level - it does not appear that the packages actually get physically split out in the database, they are extracted from the obejct data that belongs to the project. SELECT internal.packages.*, internal.object_versions.* FROM internal.packages JOIN internal.object_versions ON internal.packages.project_version_lsn = internal.object_versions.object_version_lsn Chuck
May 17th, 2012 8:48am

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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 9:11am

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:32am

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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 10:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics