1

Have been reading and following tutorials on Microsoft Business Intelligence. And i would like to clarify some things. So, as i have read and practiced, there is no technically build process like that of 'maven' or 'msbuild' kind of in MSBI. i am more interest on the utilities viz: dtutil.exe and the dtexec.exe; so that i may hopefully automate the process of deployment. Some questions:

  1. Deployment here is more kind of move the file *.dtsx on the target server/file system/store? So, my build orchestration server, will checkout the files from the version control and execute the dtutil.exe to move (aka deploy, here) to the target server?

  2. What difference does this move with dtutil.exe makes with just a manual copy and paste?

  3. If #1 is right, does it mean i have to install the SQL server/SSDT on my build server, to get the dtutils.exe OR just copying the executable from a node which have the .exe will be good enough?

  4. For the execution of the packages, (that were deployed using the dtutil.exe) can i do it via my build server on a remote server where the package were deployed?

UPDATED: MSSQL 2012; SSDT 2013

OK999
  • 1,353
  • 2
  • 19
  • 39
  • Which version of TFS are you using? Do you use xaml build or new build system? In the new build system, it's easy to specify the tool you want to run in a Command line task, check: https://www.visualstudio.com/en-us/docs/build/steps/utility/command-line – Cece Dong - MSFT Jan 10 '17 at 09:10

1 Answers1

2

What version of SSIS? If you're talking DTUtil.exe then I guess you mean 2008 or SQL 2012 package deployment (not Project deployment)

There's a big difference between 2008 and 2012.

In 2012 onwards there is a more mature execution platform. Most if not all functions are achieved via SSISDB stored procedures, i.e. this: https://msdn.microsoft.com/en-us/library/jj820152.aspx

There are a couple of deployment options in 2008 but to my mind there is no benefit in deploying to msdb (which requires DTUtil.exe), so I just do a file copy to deploy to a runtime folder (doesn't even need to be on the SQL Server but it makes things simpler).

Then you use DTExec.exe to run the package.You might want to use the /Va option to validate as part of your deployment.

In the past I have also written a text crawler that sucks out and logs the version numbers out of packages so you can see the package versions.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • I think the SP way of deploying is applicable to project deployment model & not for package deployment. https://msdn.microsoft.com/en-us/library/bea8ce8d-cf63-4257-840a-fc9adceade8c(v=sql.110) .. The new team that i joined is using the packge deployment model. any thoughts? – OK999 Jan 09 '17 at 23:01
  • So specifically you are using SQL 2012 package deployment. That's pretty similiar to SQL 2008 then. Is there a reason they are sticking with package deployment - project deployment has a lot of benefits with not a lot of downside. – Nick.Mc Jan 09 '17 at 23:11
  • With regards to SQL 2012 package deployment are they running .dtsx from the file system or using msdb. There are many different deployment and runtime options and you'll need to tailor your deployment to this. – Nick.Mc Jan 09 '17 at 23:13
  • I think, they are using the file system based deployment. And jobs to execute. So, i am trying to simplify this process of manual copy and job based execution; to a script based deployment (which i think is just a copy using the dtutil.exe) and either execute the package using dtexec.exe or leave it to the scheduled jobs for the time being. – OK999 Jan 09 '17 at 23:20
  • Basically the .dtsx file that you edit in Visual Studio (and check in to version control) is the thing that `DTExec.exe` executes. I don't know your environment but most likely there isn't a one-to-one relationship between packages and SQL Agent Jobs. Some jobs might execute multiple packages. Some packages might be in multiple jobs. Some packages aren't executed by jobs. Often there are just a couple of jobs executing 'master packages' that call other packages. So I don't know if you can automate the SQL Agent side of things. – Nick.Mc Jan 09 '17 at 23:27
  • In my prior job (where we used SQL 2008, which is basically sql 2012 package deployment), we created a batch file that you could add to the VS tools menu which coped the .dtsx file to the runtime folder and that was very useful. If you are working towards a 'continuous integration' process then I guess you want to get the packages out of TFS, validate them and copy them to the runtime area. – Nick.Mc Jan 09 '17 at 23:28
  • And so, with the package deployment model are you suggesting/OK with the dtutil.exe? When you say "runtime area" is it the folder/directory where the packages are deployed to (in a File system based deployment) by the using dtutil.exe in my case? Finally, what needs to be installed on the Continous integration server to perform the package validation? Thank you! – OK999 Jan 09 '17 at 23:35
  • Don't use `dtutil` at all if you are using a file based deployment. You just do a file copy. Check the command line in the SQL Agent jobs. If it uses the `/F` switch, it means it is loading the package from the file system. You just need to copy the package as a file, into the path referred to by the `/F` switch. (What I call the 'runtime area'). To validate packages you'd need to basically install the SSIS runtime of the SQL Server CD (and I think there is a licencing cost). In some cases though a package will not validate (because an import file doesn't exist), and this is a valid case. – Nick.Mc Jan 10 '17 at 00:04
  • Do you know how your team is utilising package configs? These are external objects (usually files) referred to by packages that configure the package at runtime, i.e. tell it which data server to connect to etc. Just make sure you're across that. If everything is set up properly, you don't have to worry about it too much though. – Nick.Mc Jan 10 '17 at 00:06