SSIS Deployments

  • Comments posted to this topic are about the item SSIS Deployments

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

  • Caveat that I haven't looked into this at all, but I'm curious if there is an option to deploy through TFS via an automated build?

  • How abou the Deployment Utility that you can generate when building the package? I do not have 2012 to test but 2008 has this built in.

    Right click on the Project and select Properties, select the Deployment Utility and set the CreateDeploymentUtility to True.

    This will create a deployment utility under the bin folder of the project. You just double click to start the utility.

  • It is possible to automate deployments of SSIS projects/packages through TFS. I am on the tail end of a proof of concept project of deployment of SSIS, SSRS, SSAS, and Schema from TFS build definitions.

    I made use of several open source MSBuild extensions:

    SSIS: http://sqlsrvintegrationsrv.codeplex.com/

    SSRS: ssrsmsbuildtasks.codeplex.com

    SSAS: http://msbuildextensionpack.codeplex.com/ (For the use of Devenv.exe via command line)

    This is all against SQL Server 2012 instances as well.

  • These methods are good, but how to add multiple packages in different folders?

    Like PackageA will go in folder A, PackageB will go under folder B in MSDB.

  • Nice work Koen

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The only criticism I have, and this is common to a LOT of technical articles, is that this does not consider the scenario that you have to deploy an package to an environment you have no control over and know nothing of at design time, or if you have configuration values that need to be set as install time. Having faced exactly this problem where we need to deploy packages to customer sites (often the deployment is done by the customer themselves, or sometimes by one of our 'consultants', none of whom have the first idea how SSIS works) we opted for the command line option because it allows us to be a bit more intelligent. We have to supply both 2005 and 2008 versions of our package because our systems support both and with command line installation we can determine the version of the SQL server automatically and deploy the appropriate package. We can also determine the compatibility level of the target DB which is useful because we use some CLR functions and the number of people who upgrade a SQL 2000 DB and leave the compatibility level at 80, thus rendering CLR functions inoperable, is depressing.

    Sadly the greatest limitation is the inability of the dtsinstall tool to specify config values as part of the install command meaning that users have to manually edit config values during installation which is very annoying (incidentally, if anyone know of a way to do this I'd love to hear it!).

  • Good point. How do you deal with encrypted packages if a third party is installing the package? Are you force to give the password away to the third party to be able to make the required configuration changes?

  • Simple answer is that we don't: We leave our package unencrypted because there is nothing earth shakingly proprietary about it and clients own the data do there's nothing to hide. If we did have to deal with encrypted packages I'd probably smash my head against my desk. Actually considering dumping SSIS got this because our Oracle equivalent is just a bunch of procs and its easier to install and use and its quicker too!

  • Thanks for the practical answer. And, therefore it becomes futile to protect the package from being edited by a third party (beyong config changes).

  • rustman (4/11/2013)


    How abou the Deployment Utility that you can generate when building the package? I do not have 2012 to test but 2008 has this built in.

    Right click on the Project and select Properties, select the Deployment Utility and set the CreateDeploymentUtility to True.

    This will create a deployment utility under the bin folder of the project. You just double click to start the utility.

    This is the first method I describe, under "The Traditionalist Way".

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

  • apnesh.d (4/11/2013)


    These methods are good, but how to add multiple packages in different folders?

    Like PackageA will go in folder A, PackageB will go under folder B in MSDB.

    What you can do is to create an intelligent batch script.

    You put the packages in seperate folders and you give the folders the name you want to have in the MSDB database. (in other words, you mimic the file structure of the MSDB on your computer)

    In the batch script, you loop over the folders and you put the foldername in a variable. You check if the folder exists on MSDB and if not, you create it using dtutil.

    Inside each folder, you loop over the packages and you deploy them using dtutil.

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

  • You can specify the folder after the SQL parameter and before the package name in DTUTIL ( i.e. "Folder A\PackageName").

  • Thanks Koen. As a production DBA, I prefer the methods that don't involve BIDS/SSDT, since I don't want to be opening packages in the design tools just to deploy them.

    In the old school method, you mention that you don't need to add ".dtsx" on to the package name. That's true when you deploy to SQL Server, but if instead you use dtutil to deploy to the file system, you need to supply the whole path and file name.

    John

  • John Mitchell-245523 (9/25/2015)


    Thanks Koen. As a production DBA, I prefer the methods that don't involve BIDS/SSDT, since I don't want to be opening packages in the design tools just to deploy them.

    In the old school method, you mention that you don't need to add ".dtsx" on to the package name. That's true when you deploy to SQL Server, but if instead you use dtutil to deploy to the file system, you need to supply the whole path and file name.

    John

    Thanks for the remark.

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

Viewing 15 posts - 1 through 15 (of 25 total)

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