July 26, 2006 at 9:14 am
When I try to expand the MSDB under Stored packages in preparation to deploy an SSIS package to another server, I get the following.
?Client unable to establish connect.
Encryption not supported on SQL Server
The SSIS configuration file is the following:
-
-
-
-
Any help appreciated
July 27, 2006 at 6:21 am
Hi Barry,
I have not experienced this issue, but I found the following information that may be helpful: http://sqljunkies.com/Forums/ShowPost.aspx?PostID=4597.
To put it succunctly, it's difficult (if not impossible) to move SSIS packages containing sensitive information (connection passwords, etc.) around the enterprise with alacrity.
I've had success installing SSIS packages to the file system. I follow a three-step process to make them execute successfully:
1. When developing the package, navigate to Package properties. Under Security, locate the PackagePassword and ProtectionLevel properties. There's lots of good information out there on ProtectionLevel, but the most portable setting (imo) is EncryptAllWithPassword. After setting this, click the ellipsis beside the PackagePassword property and enter (and confirm) a password. This action uses the supplied password as a key to encrypt all sensitive data in the package.
2. Richt-click the Solution in Solution Explorer and click Properties. Under Configuration Properties, click Deployment Utility. Change the CreatDeploymentUtility property to True. Click OK. Right-click the Solution again in Solution Explorer and click Build. Navigate to the solution folder in Windows Explorer. In the \bin folder, you should now find a \Deployment folder. It will contain at least two files: One for each dtsx package and one named <SolutionName>.SSISDeploymentManifest. Double-click the .SSISDeploymentManifest file and install the package in the File System - making note of the package installation full path.
3. In SQL Server Management Studio, navigate to SQL Server Agent\Jobs. Create a new job and job step. Enter the following command in the step (include quotes):
"<DTExecFullPath>\DTExec.exe" -F"<PackageInstallationFullPath>" -De<PackagePassword>
(The DTExecFullPath defaults to <InstallationDrive>:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe)
If you need more assistance, feel free to contact me directly at andy.leonard@gmail.com.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply