none
SQL 2012 SSIS Single Package Deployment RRS feed

  • Question

  • 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 

    Friday, April 27, 2012 3:54 PM

All replies

  • 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

    Friday, April 27, 2012 4:05 PM
  • 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?

    Friday, April 27, 2012 4:50 PM
  • 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

    Friday, April 27, 2012 6:18 PM
  • 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

    Monday, May 14, 2012 3:22 PM
  • 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?
    Wednesday, May 16, 2012 11:34 AM
  • Well, if you dont think this is one of approaches.

    DTutil might be the other you can try.


    Derek

    Wednesday, May 16, 2012 3:30 PM
  • 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.

    Thursday, May 17, 2012 12:27 PM
  • 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



    Thursday, May 17, 2012 12:45 PM
  • 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

    Thursday, May 17, 2012 1:08 PM
  • 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:

    1. Navigate to the Catalog and do an Export on the Project to create an ISPAC file
    2. Go to Data Tools and create a New Project selecting the Integration Services Import Project Wizard
    3. Select 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 project
    4. In the opened project, add the package with the update that you are looking to deploy, keep the other packages as is
    5. Deploy 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.

    Thursday, May 17, 2012 2:29 PM
  • 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

    Thursday, May 17, 2012 2:33 PM
  • 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.

    Friday, July 13, 2012 3:04 PM
  • 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.

    Friday, July 13, 2012 3:06 PM
  • 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.

    Friday, July 13, 2012 3:07 PM
  • 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!!

    Thursday, August 23, 2012 12:38 AM
  • 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.

    Thursday, August 23, 2012 5:48 AM
  • It is a real drawback that we cannot deploy a single package. If anyone has a workaround please let me know!


    Thanks! Josh

    Wednesday, April 16, 2014 6:11 AM
  • It is a real drawback that we cannot deploy a single package. If anyone has a workaround please let me know!


    Thanks! Josh

    Workarounds:

    * switch to package deployment model

    * use branching in your versioning system to create a new branch. Modify only one package and then deploy the project.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, April 16, 2014 6:15 AM
  • I am not sure why MS came up with this model, there should be an easy way for single package deployment. This is taking away functionality that previous version had. I know this is a completely new way of deploying but whoever came with this idea obviously does not work in the real world.

    Thanks,

    Mukti


    Mukti

    Tuesday, September 16, 2014 8:25 PM
  • Mukti,

    1. Save a copy of your modified package in your client disk.

    2. Import the project from the SSIS catalog again.

    3. Replace the original package for the modified one. Leave the package which development is underway as it is.

    4. Deploy the project to your SSIS catalog.

    5. Resume work on the project you were working on in the first place.

    Tuesday, September 16, 2014 9:45 PM
  • I am not sure why MS came up with this model, there should be an easy way for single package deployment. This is taking away functionality that previous version had. I know this is a completely new way of deploying but whoever came with this idea obviously does not work in the real world.

    Thanks,

    Mukti


    Mukti

    Actual developers in the real world (.NET, Java, ...) have been using branching/merging and continuous deployment/integration for years. The BI development world is years behind on this.

    Ask yourself this: when developing an application, can you deploy a single function? No, you need to deploy the entire compiled code as well. Just the same.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, September 17, 2014 6:30 AM
  • I agree, but SSIS is very different from application development, the whole project is not "one" application. Every package in the project might be related in some way but they are independent entities. That's the reason for years we have been using single package deployment. I understand there are ways you can do single package deployment but none of them is straight forward. My point is why take functionality away with new releases, new versions should make development easier not force something upon developers.

    Thanks,

    Mukti


    Mukti

    Wednesday, September 17, 2014 4:22 PM
  • The point of project deployment is that it exactly becomes like application development. Packages are no longer independent entities, but part of a bigger picture.

    If a package is really independent of all the other packages, it should be in a different project.

    If you really miss the functionality of single package deployment, you can still convert your project to the package deployment model.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, September 17, 2014 7:11 PM
  • This one limitation took away all excitement of SSISDB.  Also highlights the one more stupidity of MSFT.  The project analogy argument above is absurd too.  "Modularity" ring?  Why would I need to test /screw up all parts of software functionality to just inject one simple function or module.

    Any news on providing incremental deployment feature in future release?



    Mahesh

    Friday, October 10, 2014 9:30 PM
  • Yes, it is possible and it is very typical scenario in enterprise settings.

    But it will require to setup some source code control solution, like Team Foundation Server.

    Simplest scenario:

    1. Setup source control systems (TFS, SVN or any other) or use Visual Studio online (free to up to 5 users).

    2. Developer check out package he/she is working on from source control (OK to check out both packages).

    3. Only done packages are check-in (you may use more robust scenarios to check in daily and use branching to separate production code).

    4. You deploy only checked-in versions of the packages, using separate workspace in Visual Studio.


    • Edited by Piotr Palka Friday, October 10, 2014 10:34 PM
    Friday, October 10, 2014 10:33 PM
  • That's a workaround which is not safe in project dealing with 10s & 100s of packages.  Any mistake by any developer / wrong version checkin-checkout will cost organization handsome in term of data corruption, business loss, etc.

    Plus you are adding to the cost, you need well disciplined version control & Testing processes in place besides the source control s/w - which again need experienced people resource.  Not every shop is equipped or afford it. 

    Has MSFT got this feedback (any connect link?) and working on it in future release or patch for incremental deployment feature?


    Mahesh


    Saturday, October 11, 2014 12:47 PM
  • I proposed this for one project with two packages.

    For bigger solutions it should be at least 2 or 3 steps project: developers check in code in development branch, it is frozen using code branching in the source control (moved to main / trunk branch), code from this barnch is verified, tested and then deployed.

    If we don't have source control "any mistake by any developer / wrong version" given to DBA for deployment will lead to exactly the same problems.

    Verification DBA would do before deployment now you can do on merge.

    For me "well disciplined version control & Testing processes" is a must in any shop. How much you want to test is up to you, but you need to have process in place.

    Saturday, October 11, 2014 3:51 PM
  • Plus you are adding to the cost, you need well disciplined version control & Testing processes in place besides the source control s/w - which again need experienced people resource.  Not every shop is equipped or afford it. 

    Has MSFT got this feedback (any connect link?) and working on it in future release or patch for incremental deployment feature?


    Mahesh


    TFS Express is free and should have most functionalities you need.

    Basically your reply says: "we'd like to be more like cowboys with our ETL, because the disciplined way is too hard".

    MSFT (or the SSIS team in particular) made a very conscious decision with this one. I'm not sure they will change it soon.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Monday, October 13, 2014 6:42 AM
  • Basically your reply says: "we'd like to be more like cowboys with our ETL, because the disciplined way is too hard".

    It was not about discipline/indiscipline. The point was not all shops have knowledge or equipped with all tools and standards (esp. for workaround processes).  To work with one ETL tool I don't want to train staff on 10 other tools and skills, just bz a simple feature was not provided in the product?

    Thanks


    Mahesh

    Sunday, October 26, 2014 8:05 PM
  • I agree completely with Mahesh.

    Ever since the days of UNIX makefiles, only those parts of a project which have changed have been recompiled. 

    Indeed for instance, Visual Studio does this by default when building .NET projects, C++ etc - only what has changed is rebuilt, I mean it makes sense. Why waste time rebuilding something that hasn't changed at all. 

    I do not understand why an SSIS project requires the full redeployment of every single package when only one may have changed - it seems incredibly risky and exposes us to inadvertently deploying changes in other packages that we don't want to. 

    The workarounds described above that involve branching/merging etc in order to only be able to deploy a portion of a SSIS project may work to reduce that risk - but they can hardly be called 'best practice'. We risk end up making a mess of our version histories. 

    It would be good to get some direction from Microsoft on why it has been implemented the way it has, perhaps there is something we don't understand - in particular the decision to compile all packages into a single .ispac - after all, that's the source of our problem here. 

    It would be handy to have a smaller target unit of compilation - again, forgive my possible lack of understanding here, but what is the reason for the granularity of deployment to be at the project level, and why wasn't the target of deployment simply kept at the package level?

    That was the massive change from 2008R2..


    Thanks! Josh





    Sunday, October 26, 2014 8:52 PM
  • Mukti,

    I entirely agree with you, deploying a single Package would be very helpful 

    regards  John James

    Thursday, May 7, 2015 11:19 AM
  • Well, luckily MS listened to you guys complaining, because incremental project deployment will be available in SSIS 2016. :)

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Monday, May 11, 2015 8:46 PM