April 11, 2013 at 12:05 am
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
April 11, 2013 at 5:42 am
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?
April 11, 2013 at 7:14 am
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.
April 11, 2013 at 12:36 pm
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.
April 11, 2013 at 1:21 pm
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.
April 11, 2013 at 8:47 pm
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
April 12, 2013 at 3:42 am
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!).
April 12, 2013 at 11:57 am
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?
April 12, 2013 at 1:27 pm
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!
April 13, 2013 at 8:54 am
Thanks for the practical answer. And, therefore it becomes futile to protect the package from being edited by a third party (beyong config changes).
April 15, 2013 at 1:31 am
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
April 15, 2013 at 1:34 am
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
April 20, 2013 at 10:59 am
You can specify the folder after the SQL parameter and before the package name in DTUTIL ( i.e. "Folder A\PackageName").
September 25, 2015 at 5:14 am
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
September 25, 2015 at 5:22 am
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