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.

SSIS Cheatsheet

1 Comment

I infrequently use SSIS, but once in awhile I need to edit a package. This is harder than it was with SQL Server 2000, in which one could crank up Enterprise Manager, go to the target instance, drill down to a DTS package and start editing away, with changes saved directly to the database.

Here’s my newly created cheat sheet:

SSIS has lots of functionality and this is just a very bare bones description of opening an existing package. Note that this does not discuss deploying multiple packages all at once (deployment wizard) or details on editing and debugging SSIS packages. Nor does it weigh the merits of
storing SSIS packages on the file system vs in the database vs SSIS store.

SSIS Packages are eventually get stored and executed within SQL Server. They can be stored on the file system or within the SQL Server database, typically in MSDB.

SSIS Packages get edited in Visual Studio. To do that, you must get a copy of the SSIS package on to the file system. Several ways to do that. From within Visual Studio, you can create a new SSIS package, or you can choose Project – Add existing package and get one directly from SQL Server or a file system location. Or you can go to SQL Server Management Studio and export there first and then go into Visual Studio.

When done editing in Visual Studio, one must deploy to SQL Server. There are a few ways to do that. One can use the deployment wizard, or import the package using SQL Server Management Studio’s import package, or use the command line dtutil tool. There is also a dtexec for commandline execution of SSIS packages.

Once deployed, SSIS packages can then be run in SQL Server via jobs managed by SQL Server Agent.

Step 1: Start Visual Studio and Make a New Project
File – New Project
Integration Services Project
Use Location to create directory structure meaningful for your project and team’s coding conventions.

Step 2: Add an Existing Package to the Project
Project – Add and Existing Package
In the Add Copy of Existing Package dialog:
Package Location is SQL Server
Server (local) if you are logged on to the SQL Server machine. If working from laptop, identify the SQL Server instance and include port if necessary
(e.g. USEOMAPD357,1113)
Authentication: Windows
Package Path: Click on . and locate package.
Then click OK.
Nothing will change on screen, but a file will be on the file system containing the package which you can open in the next step.

Step 3: Open the package for editing.
File – Open
locate the newly exported package.

Step 4: Edit the package.
Use Visual Studio as needed to edit.
You can test the package as needed.
You will find the SSIS programming metaphor is roughly similar to DTS and other Microsoft visual editing tools.
Save changes to package and they will update the file on the operating system.

Step 5: Deploy the package
Start SQL Server Management Studio.
Connect to Integration Services
Click on Stored Packages
Click on MSDB
Right Click and choose Import Package.
Package Location will be File System
Use Package Path to identify the file you edited in Step 4 above.
Click on Package Name and provide a name or accept the existing one.
Make protection level Encrypt Sensitive Data with User Key.