CODEWITHSUNDEEP
sql-server-2005ssis
sgibbons
sgibbons

Reputation: 3630

How to fix the error that occurs during execution of the package deployed on a server?

I've created a dtsx package with Sql Server Business Intelligence Development studio, and I am executing it using the dtexec utility. Using 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.

Upvotes: 1

Views: 13669

Answers (6)

renaissanceMan
renaissanceMan

Reputation: 433

If you are getting a config file message at run time in your log file like "The configuration file \\xx\yy\blah.dtsConfig cannot be found" , be very aware that the message can be bogus. Or the message "Failure importing configuration file "\\xx\yy\blah.dtsconfig"". Had a perfect run with 20 variables set at runtime by dtsconfig and the message above was 3 or 4 places in the log file for that successful run. I will say, it's only a warning. It even gives this message and then shows variable references in the dtsconfig file that it says doesn't exist. If this is the first time you have seen it complain about dtsconfig, your impulse is to say "oh, that's why it's not working". Ignore it.
With this package I had just recently added the configuration file in the gui/designer as opposed to only specifying it as dtexec /Conf parameter. That must confuse it somehow.

Upvotes: 0

Huske
Huske

Reputation: 9296

Here are the guidelines from MSDN about package security. Setting the Protection Level of Packages

Upvotes: 0

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

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.

Upvotes: 6

Jobo
Jobo

Reputation: 916

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.

Upvotes: 1

Dale Wright
Dale Wright

Reputation: 119

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.

Upvotes: 1

baldy
baldy

Reputation: 5541

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.

Upvotes: 1

Related Questions