SSIS Deployments

  • I think what needs to be discussed/understood is that SSIS packages can exist and run as part of a production environment without living on a SQL server. I believe that most developers and DBA's think that SSIS packages need to be deployed/copied to a SQL Server.

    Not true!

    See the following:

    http://sqlblog.com/blogs/piotr_rodak/archive/2012/04/09/running-ssis-packages-from-c.aspx

  • At a minimum I think SSIS (MsDtsSrvr.exe) would need to run on the box.

    Ken

  • Great post Koen!

    In the comments, it seems there is a bit of confusion between deployment and execution...basically, packages don't have to be "deployed" in order to be "executed". I've been in plenty of shops where the packages sit in a directory (typically on a server) and are executed via DTEXEC script.

    It should also be pointed out that Koen specifically referenced the scenario of deploying to a "development/test" environment in the second paragraph. Thus the argument(s) that the "pragmatic method is not pragmatic because devs shouldn't have access to the production server" (also addressed in same paragraph) is/are out of context. As a side note, I've been using BIDSHelper for so long that sometimes I have trouble distinguishing between built in functionality and BIDSHelper features.

    Finally, as SQL2012 adoption starts picking up speed, I expect a lot of companies to move away package deployment and adopt the new project deployment model. There are so many benefits ...many already mentioned in earlier comments... it's an easy sell. From a dev perspective, not having to wire up a custom logging/auditing framework saves a good chunk of time...time which can instead be spent adding indexes to the SSISDB database 😛

  • tim.gray 23818 (4/10/2013)


    I don't think the "Pragmatic" way is actually very pragmatic. In lots of places, developers aren't supposed to be touching production servers and development tools should not be installed on production servers. So using a development tool for deployment is not feasible. And if you are building your SSIS package as part of a product that is installed by implementation guys at a customer site, the BIDS options are not available.

    You're right, developers shouldn't deploy to production. To be clear, I don't do it either.

    I wrote this article some time ago and when I reread it now, I think I didn't stress enough that some deployment methods should only be used to get SSIS packages from your local development machine onto the development/test server. Certainly not production.

    BIDSHelper is currently my preferred method for getting my packages on the dev server, but in SSIS 2012 I just use the built-in functionality.

    This article is just a short introduction, there might be articles following with more details about some deployment methods or how to deploy packages in SSIS 2012.

    edit: forgot to add "thanks for reading and taking the time to post a commment" 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • reuben.anderson (4/10/2013)


    p.s. all of which has meant I struggle to understand why an Integration Server is needed at all. An engine to execute packages may as well be included in the SQL Agent, or SQL Server, or just out of process...

    I'm sure I'm missing something though.

    To fully use SSIS packages, you need to install the SSIS service.

    I did some research in this blog post:

    When is DTEXEC installed?

    You can disable the service after the installation though, you just need the binaries.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Meena Ravi (4/10/2013)


    How to deploy several packages by a batch job?

    dtsx and configuration files?

    You use dtutil.

    It's a command line utility to deploy packages.

    In a batch job you can just loop over all the .dtsx packages and call dtutil for each package.

    You can find an example here:

    http://blog.ponytailbob.com/2010/07/deploying-ssis-packages-with-batch-file.html

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Great article, but probably needs to have a disclaimer since it doesn't necessarily apply to 2012.

  • Keith Hays (2/12/2014)


    Great article, but probably needs to have a disclaimer since it doesn't necessarily apply to 2012.

    The intro states:

    This introductory article describes various methods on how you can deploy your individual packages stored on your local desktop into the server. The project deployment model, introduced in SQL Server 2012, is outside the scope of this article. However, the package deployment model of SSIS 2012 can use the same methods described here.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sorry, must have missed it.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply