November 11, 2011 at 1:54 pm
I am trying to get an SSIS package someone else wrote deployed on our server. Before I do that, I made sure I could run it manually in BIDS. It ran successfully. Just for background, it runs an EXECUTE SQL task, followed by a TRANSFER SQL SERVER OBJECTS task and ending with another EXECUTE SQL task. The TRANSFER SQL SERVER OBJECTS copies several databases and other database objects (but not all) from one SQL server to another.
I set the Protection Level to EncryptSensitiveWithPassword (this has worked just fine for me on other SSIS packages) and created an XML Package Configuration file which gets built down on the server when I build the package.
However, after building it, logging on to the server (using my ID that does have access to the SQL server, I click on the .SSISDeploymentManifest file and attempt to deploy it. I make sure I click the Validate Packaghes after Installation box and tell it where I want it deployed to. When it starts installing, it starts validating things. The first EXECUTE SQL task validates ok, but the TRANSFER SQL SERVER OBJECTS task fails with the error:
Error: The Validate method on the task failed, and the returned error code 0x80131501 (failed to connect to server MYSERVER). The validate method must succeed and indicate the result using an "out" parameter.
The Package Configuration file has the login information needed in each connection string. It just fails at this validation step.
On a side note, like I said, I can run it manually in BIDS on my PC. However, once I create the Package Configuration file, if I then try to run it again on my PC, it fails with this same error. If I go back to the SSIS->Package Configurations... drop down and REMOVE the file I created, it then runs on my local PC again.
Does anyone have any ideas why this is failing like it is? I've run out of ideas and could use help. Thank you in advance!
November 14, 2011 at 12:42 am
Does the config file contain sensitive information, such as passwords?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 5:18 am
yes, though I had to add them in
November 15, 2011 at 11:24 pm
If you change the protection level to DontSaveSensitive and keep the configuration file (which has the sensitive info), does it work?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2011 at 5:24 am
I had the user rewrite the SSIS package and replace the Transfer SQL Server Objects task with several Data Flow tasks. Then I was able to deploy the package just fine. I think it had to do with that specific task. It wouldn't validate when I started to deploy it. I don't know if it was being incorrectly used or if there's actually a problem with it. Either way, we have a working package now. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply