July 27, 2006 at 8:29 am
Hi,
I am deploying a SSIS package to a live environment for the first time.. I have copied the dtsx files onto the server, and I have created a batch job for the package.
When I try and run the package within SQL Agent, it fails saying that it can't connect to the server with the user given.
I have manually run the package and it runs fine!
When I go into the package and look at the individual connection, I am using SQL Server authentication, but the password is blank, even though the Save my password is clicked. I am completely stuck now...
I have also tried adding an XML configuration file,and made sure the password is within the connection string, but it still doesn't work.
Can anyone advise as to how to fix this.
Thanks
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
July 31, 2006 at 4:13 am
In the package, what protectionlevel was used? The default is EncryptSensitiveWithUserKey - which, unless you developed, and deployed witht he same user that is trying to execute the job, is not going to work 🙁
I've had a similar problem trhying to deploy to test (mine aren't near production yet). One of the ways around this is to change the package property to: EncryptSenstitiveWithPassword. Then alter the command line that executes the package to add /DECRYPT YourPasswordHere
That should work, although the jury is out re: the safety of this, since your password for your package in is a plain text file, although if someone suspect has already got access to the file system on the machine, chances are you have bigger problems. There are several other options, like EncryptWithRegistryKey (iirc), but I haven't tried these....
HTH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply