dtutil, named instance and package import

1 Comment

I was trying to get an SSIS package imported into a named instance using the command line utility dtutil and couldn’t get it to work.

This command had been working just fine with a default instance.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy DTS;"MSDB\Maintenance Plansuser\dbbackup"

But when running the same command on a named instance, I got the following:
dtutil /File userdbbackup.dtsx /DestServer hostnameinstancename /Copy DTS;"MSDB\Maintenance Plans\userdbbackup"

Microsoft (R) SQL Server SSIS Package Utilities
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.

Could not create SSIS Service Storage object because of error 0x800706BA.
Error (0x800706BA) while checking for the existence of package “Maintenance Plansuserdbbackup” in the SSIS package store.
Could not create SSIS Service Storage object because of error 0x800706BA.
Description: The RPC server is unavailable.

The solution was changing the DTS flag to SQL in the /COPY parameter. I also had to change the folder location by removing MSDB.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy SQL;"\Maintenance Plans\userdbbackup"

This means the package is getting stored within MSDB in the SQL Server rather than the SSIS Package Store.

Note that the commands above do not specify username and password. The commands were run on the local host with a privileged user that had a trusted connection to the DB.

Here are some links on the topic of getting the SSIS Package Store to work with Named Instances:  Robert Davis explains and Tim Cullen covers similar ground.

If I understand the above links correctly, if I manipulate the XML files as they indicate, the dtutil /COPY DTS can be made to work with named instances.

By the way, this concise dtutil tutorial helped steer me in the right direction. It even mentioned dtexec and dtexecui, both new to me.

SQL Server connection protocols and named instances

Leave a comment

I’ve been working on a project that involves automating tasks on SQL Server installs. Things got a bit messy when working with multiple instances on the same host.

To automate sql scripts via a VB.NET program, I ended up using shared memory connections. The code will always run locally on the host where the instances reside, so no problem there. I learned the following about SQL Server connection string behavior:

1. This will always use the default instance with a shared memory connection. Parentheses are required.
Data Source=(local); Integrated Security=True

2. This will allow a connection to a named instance via shared memory. Note there are no parentheses.
Data Source=HOSTNAME\NAMEDINSTANCE; Integrated Security=True

3. This will force a connection over TCP/IP on the port specified, provided the SQL Server Network Configuration has been set to the port indicated.
Data Source=HOSTNAME\NAMEDINSTANCE,port; Integrated Security=True

The above connection strings can be used in data access code in VB.NET and other languages.

Want to force a shared memory connection from sqlcmd? You can append the prefix lpc:
sqlcmd -S lpc:HOSTNAME\NAMEDINSTANCE -E

Although
sqlcmd -S HOSTNAME\NAMEDINSTANCE -E
does the same thing when executed from the local host. When executed remotely, you likely will need the port to appended after NamedInstance.

For other SQL Server protocols such as Named Pipes, there must be additional abbreviations, but I haven’t dug into that…

Lastly, one can tell from within SQL Server what protocols are used by what sessions. The view sys.dm_exec_connections shows that, specifically the net_transport column. See the below query which joins to sysprocesses.

select d.net_transport, s.spid, s.program_name, s.nt_username, s.hostprocess, s.lastwaittype, d.client_tcp_port, d.protocol_type, d.protocol_version
from sysprocesses s
inner join sys.dm_exec_connections d
on s.spid = d.session_id