Confused about DTUtil from 2008 R2 running on a remote server.

Question

My database installer that currently deploys .sql files using sqlcmd now will also need to deploy a SSIS .dtsx file. After some research it appears that the DTUTIL.exe command line utility is provided to perform this task. My customer does not allow installation directly on the SQL Server so we use another server as the deployment server. Our MSI installs the .sql and .dtsx files to the file system of the deployment server and then uses sqlcmd to execute the .sql files and now DTUtil to /COPY SQL the .dtsx files to the IS server.

We are currently using the SQL Server 2008 R2 SP1 version of SSIS so we have installed the client tools from the SQL Server 2008 R2 setup media including…

• Business Intelligence Development Studio
• Client Tools Connectivity
• Client Tools Backwards Compatibility
• Client Tools SDK
• Management Tools – Basic
• Management Tools Complete
• SQL Client Connectivity SDK

… on the deployment server and then applied SQL Server 2008 R2 SP1.

When I run the DTUtil command it always returns the following message indicating Integration Services needs to be installed…

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>dtutil /?

Microsoft (R) SQL Server SSIS Package Utilities

Version 10.50.1600.1 for 64-bit

Copyright (C) Microsoft Corporation 2010. All rights reserved.

The SQL Server SSIS Package Utilities require Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.

It appears DTUtil.exe has a dependency on integration services. 

So I ran the SQL Server 2008 R2 setup again and added “Integration Services” to this server.  I now have “SQL Server Integration Services 10.0” service installed with Status: Started and Startup Type:Automatic.  I immediately stopped the service and change Startup Type to Disabled.

Now when I run DTUtil I am able to use the /COPY SQL command to push the .dtsx file to the server.  So just having “Integration Services” installed but not running seems to satisfy the dependency of DTUtil.

What is strange is that SQL Server Management studio on the same server was able to import the .dtsx file to the server even before I installed “Integration Services” so it does not seem to have the same dependency as DTUTIL.

On another computer that also didn’t have SQL Server or Integration services I got the same results for the 2008 R2 version of DTUtil.exe (V 10.50.1600.1 in the C:\Program Files\Microsoft SQL Server\100\DTS\Binn folder).

I then tried the version of DTUtil that installed with SQL Server 2012 tools (V. 11.0.2100.60 in the C:\Program Files\Microsoft SQL Server\110\DTS\Binn folder) and that version on the same system was able to run without Integration Services installed.

So it appears the dependency on “Integration Services” has been removed for the 2012 version of DTUTIL.exe. Can someone confirm this?

But unfortunately I am in the situation where I must use the 2008 R2 version of DTUtil because my customers are only required to provide SQL Server 2008 R2 SP1 for our product and would not agree to purchase 2012 licenses for this one reason.

I have considered doing some sort of remote invoke of DTUtil on the SQL Server but that seems to have its own baggage and security implications that some customers may not allow.

So why is it that only the 2008 R2 version of DTUtil has this dependency on Integration Services?  Not SSMS and not DTUTIL (2012)

The customer will not agree to purchase another SQL Server license just to install on the deployment server to get DTUtil.exe to work. It also seems unresonable to ask them to purchase another CAL for 2012 tools.

Is there any leeway in the licensing to allow for Integration Services to be installed on a deployment server provided the it is not running as a service, only installed to satisfy the dependency of DTUtil to copy a .dtsx file to a remote server which is properly licensed?

What are the options here?

Thanks

Answer 1

What is strange is that SQL Server Management studio on the same server was able to import the .dtsx file to the server even before I installed “Integration Services” so it does not seem to have the same dependency as DTUTIL.

On another computer that also didn’t have SQL Server or Integration services I got the same results for the 2008 R2 version of DTUtil.exe (V 10.50.1600.1 in the C:\Program Files\Microsoft SQL Server\100\DTS\Binn folder).

I then tried the version of DTUtil that installed with SQL Server 2012 tools (V. 11.0.2100.60 in the C:\Program Files\Microsoft SQL Server\110\DTS\Binn folder) and that version on the same system was able to run without Integration Services installed.

So it appears the dependency on “Integration Services” has been removed for the 2012 version of DTUTIL.exe. Can someone confirm this?

I can't confirm the dependency difference between 2012 and older versions but it seems reasonable that DTUtil.exe shouldn't be dependent on Integration Services. 

You might try rolling your own deployment utility that uses SMO to save to msdb (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetosqlserver(v=sql.105).aspx).  SSMS internally uses SMO for much of it's functionality so that may explain why SSMS allows deployment even without Integration Services installed.

Alternatively, you could deploy your packages to the file system instead of msdb or the package store.  That will allow simple xcopy deployment.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

You might try rolling your own deployment utility that uses SMO to save to msdb (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetosqlserver(v=sql.105).aspx).  SSMS internally uses SMO for much of it's functionality so that may explain why SSMS allows deployment even without Integration Services installed.

Dan, Thanks for quick reply.

The link you provided to the savetosqlserver method resulted in success.

On a clean server that previously had no SQL server components installed I installed only the following two items from the SQL Server 2008 setup media …
•Management Tools – Basic
•Management Tools Complete

I then put a .net exe that runs the following sample code..

using Microsoft.SqlServer.Dts.Runtime;
 
string pkg = @"MYSSISPackage.dtsx";
                 Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
 
Package loadedPkg = app.LoadPackage(pkg, null);
                 
app.SaveToSqlServerAs(loadedPkg, null, @"MYSSISPackage", "MYSQLSERVER", null, null);

This code seems to properly copy the SSIS package to the target integration server.

On the same server I ran DTUTIL /? again and it still complains it need Integration Services installed, so I think this is working.

So maybe MS rewrote DTUTIL for 2012 to use SMO, which could explain why it also does not seem to be depended on Integration Services.

Thanks so much for your help.