March 14, 2018 at 11:35 am
I had this issue with my first SSIS package and fixed it by setting Protection Level to Dontsavesenitive and setting Creator Name to match SQL Login I was using to access the data as well as turning the Run64bitRuntime to false. I have created a second package, which does all the same tasks. I received the " Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B" when running the job in SQL Agent, and remembered this issue.
I have set all of these to be the same, yet the error still happens. I'm adding the protection level to the package properties. Is there a secondary place where this is set as well? FYI: I set the SQL password in the agent job so I don't need to create password files.
I have both SSIS packages open beside each other and see no differences. One runs in SQL Agent Jobs, one does not. I have also verified the tables involved are fully accessible by the sql account.
March 14, 2018 at 11:57 am
Luv SQL - Wednesday, March 14, 2018 11:35 AMI had this issue with my first SSIS package and fixed it by setting Protection Level to Dontsavesenitive and setting Creator Name to match SQL Login I was using to access the data as well as turning the Run64bitRuntime to false. I have created a second package, which does all the same tasks. I received the " Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B" when running the job in SQL Agent, and remembered this issue.I have set all of these to be the same, yet the error still happens. I'm adding the protection level to the package properties. Is there a secondary place where this is set as well? FYI: I set the SQL password in the agent job so I don't need to create password files.
I have both SSIS packages open beside each other and see no differences. One runs in SQL Agent Jobs, one does not. I have also verified the tables involved are fully accessible by the sql account.
There is a 'secondary place', try looking in the project properties and setting it there too.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2018 at 12:09 pm
If I select Project at the top then Properties, it's set to DontsaveSenstive.
March 14, 2018 at 12:17 pm
Luv SQL - Wednesday, March 14, 2018 12:09 PMIf I select Project at the top then Properties, it's set to DontsaveSenstive.
OK, as long as DontSaveSensitive is set in the project and all of the packages in the project, I do not know of any other place.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2018 at 12:31 pm
OH MY GOODNESS. I changed them BOTH to Encrypt, save all, closed. Opened it up again, changed Project to Dontsave, then package to Dontsave then save all. Now runs. ARgh.
March 14, 2018 at 1:04 pm
Luv SQL - Wednesday, March 14, 2018 12:31 PMOH MY GOODNESS. I changed them BOTH to Encrypt, save all, closed. Opened it up again, changed Project to Dontsave, then package to Dontsave then save all. Now runs. ARgh.
I feel the pain! Glad it's sorted now.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2018 at 1:39 pm
You can avoid all of this by switching your deployment model from package deployment to project deployment - and then deploying to the Integration Services Catalog. If you don't have that set up, I would recommend getting it set up as it provides a lot of features that can simplify not only your deployment issues - but also how you develop your packages.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 14, 2018 at 2:08 pm
I will give that a whirl. I'm fairly new to VS and the SQL Data Tools application so will see if I can do that with the tool.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply