Problems moving an SSIS .dtsx package

Question

I've created a dtsx package with Sql Server Business Intelligence Design studio, and I am executing it using the dtexec utility. Via dtexec I am setting certain properties at runtime using the /set switch. So my command looks something like:

dtexec /f "mypackage.dtsx" /set \Package.Connections[Destination].Properties[UserName];myUserName

This works perfectly when I run it on my local system (the one it was developed on). Unfortunately, when I copy this package to a different system and attempt to run this exact same command, I receive the following error:

Warning: The package path referenced an object that could not be found: \Package.Connections[Destination].Properties[UserName]. This occurs when an attempt is made to resolve a package path to an object that cannot not be found.

The new system that the package was moved to has SSIS installed and is running the same version of Sql Server as my local system (SP2). Maybe I'm misunderstanding something about the intended use of dtsx packages, but I really don't see how/why this is happening.

Answer 1

You'll need to create a deployment utility if you;re moving the package between machines. Your connection information gets encrypted using a key specific to your machine.

If you go to the project properties in VS, Select the deployment utility section and set the CreateDeploymentUtility option to true. This will create the deployment utility in the bin folder, you can then copy all that to the new machine, run the installer, and all should work fine.

Answer 2

The quickest way to move packages between machines and avoid all the signing of the packages is the following.

In Visual Studio with the package open select "Save copy of PackageName" As

You then get a wizard up. Easiest one is probably to just select file store. Then at the base of the wizard you will see protection level. Select Encrypt Sensitive data with a password. Enter a password.

On the server you wish to move it to select Import Package and it will prompt you for the password. Enter it and your connection information will be correctly move to the new server.

Definitely not best practice but it is a good method for quickly moving things around test servers.

Answer 3

On your control flow properties, there is a property called "ProtectionLevel". If you set this to 'DontSaveSensitive' then that might cause you less headaches while doing dev and testing. For production scenarios where security is a requirement then you might need to find another solution.

Answer 4

Hi,

This steps is for creating an XML configuration file (.dtsConfig) which can keep your sensitive data, like the password of your connection string, without having a Protection Level that can make you difficult to move the package from one machine to another.

In this example, assumed you have an OLE DB Connection to an SQL database called MyDb.

1. Control Flow, Property: set "ProtectionLevel" to "DontSaveSensitive"

2. Control Flow, right-click empty space to get menu: click "Package Configuration"

3. Package Configuration Organizer: tick "Enable package configuration"; click "Add"

4. Package Configuration Wizard, Select Configuration Type: set "Configuration type" to "XML configuration file"; choose "Specify configuration directly" radio button; click "Browse..."

5. Select Configuration File Location, fill "Filename": [PackageName].dtsConfig (easy if same folder and same filename as the package itself, just different extension); click "Save"

6. Package Configuration Wizard, Select Configuration Type: click "Next >"

7. Package Configuration Wizard, Select Properties to Export: traverse the following tree nodes and tick its checkbox; click "Next >"

   \[PackageName]\Connection Managers\MyDb\Properties\Connection String

   \[PackageName]\Connection Managers\MyDb\Properties\Password

8. Package Configuration Wizard, Completing Wizard, click "Finish"

9. Package Configuration Organizer: click "Close"

10. Solution Explorer: right click the root tree for menu, click "Add", "Existing Item...", click [PackageName].dtsConfig, click "Add"

11. Solution Explorer: double click \Miscellaneous\[PackageName].dtsConfig to load into editor;

12. Main menu: click "Edit", click "Advanced", click "Format Document"

13. Traverse the XML tree node: \DTSConfiguration\Configuration[Path="\Package.Connections[MyDb].Properties[Password]"]\ConfiguredValue; key in the database password; save the file

14. Windows Explorer: navigate and double click [PackageName].dtsx

15. Execute Package Utility, Configuration, click "Add", double click [PackageName].dtsConfig, click "Execute"

When required to move the .dtsx to another machine, simply accompany it with its .dtsConfig. Hope this helps.

Cheers, Ari.