11

I have 2 scheduled jobs on my SQL Server 2005 machine that are scheduled to run each morning (around 2:00 AM). These jobs have worked fine (mostly) for years and although I've had a few hiccups that I've had to work through this problem is completely stumping me.

Two mornings ago, one of my packages started reporting the following error:

Executed as user: [Service Acount]. ...n 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
     Started:  1:15:01 AM  Error: 2012-10-17 01:15:03.98
     Code: 0xC0016016
     Source:
       Description: Failed to decrypt protected XML node "DTS:Password" 
       with error 0x8009000B "Key not valid for use in specified state.". 
       You may not be authorized to access this information. This error 
       occurs when there is a cryptographic error. Verify that the 
       correct key is available.  End Error  Error: 2012-10-17 01:15:03.99
     Code: 0xC0016016
     Source:
       Description: Failed to decrypt protected XML node "DTS:Password" 
       with error 0x8009000B "Key not valid for use in specified state.". 
       You may not be authorized to access this information. This error 
       occurs when there is a cryptographic error. Verify that the 
       correct key is available.  End Error  Error: 2012-10-17 01:15:04.01
     Code: 0xC0016016     
Source:       
Description: Failed to ...  The package execution fa...  The step failed.

This appears to be a common problem, however, none of the recommendations that I've found either apply to my scenario nor does my instance seem to match most of the other cases where this occurs. Here are the important details regarding my implementation.

  • This package is exporting data from an iSeries system, to SQL Server 2005 data tables.
  • This process works successfully but keeps crashing on one, specific table export. In fact, it runs without any trouble for over 2 hours before it dies. After inspecting all properties associated with this step, I can see that there is nothing different about this step compared to the other table export steps, other than the table/column export mappings.
  • The package ProtectionLevel is set to DontSaveSensitive and the iSeries credentials are stored in a config file that is accessed by SQL Server.
  • I can execute the failing step on my machine, in BIDS. Regardless, it doesn't work on the server, though the server is using the exact same credentials.
  • As I mentioned, I have two packages. They are effectively the same thing, except one is exporting the data from one iSeries database, and the other is exporting data that is almost the exact same structure from another iSeries DB. The first package doesn't have any trouble even though it is using the same iSeries credentials.
  • To be clear, nothing on my server has changed in months (that I am aware of.) This just started happening yesterday morning.

Any tips, or thoughts would be tremendously helpful. This export is extremely important and many users/worker rely on this data for their day-to-day work.

Cœur
  • 37,241
  • 25
  • 195
  • 267
RLH
  • 15,230
  • 22
  • 98
  • 182
  • 1
    Any updates that were applied to your server? Have you tried saving the package directly from BIDS to the target location (Save As gives that option)? – rvphx Oct 17 '12 at 17:57
  • I don't believe any updates have been pushed to the Server (OS). I control any updates to SQL Server, and I haven't installed any. Also, I build the project locally on my machine, then I remote into the SQL Server machine and import it into the database (not to a file-based location.) – RLH Oct 17 '12 at 18:01
  • Have you tried changing the protection level of the package when you import it to the SQL Server? Sometimes while importing the package, that thing gets messed up. – rvphx Oct 17 '12 at 18:14
  • Well, I haven't re-imported it in a really long time. I don't know how to do that? Or do I change it on the job, and not the package. The job is explicitly configured to pull the user credentials from a config file stored off of the server hard drive. – RLH Oct 17 '12 at 18:16

6 Answers6

15

Well, I hate having to post such a response but I've solved the problem.

The short answer reason why I had this problem is because one of the fields in a data table was improperly defined. In this case it was declared as a decimal (11, 3) and it should have been a decimal (13, 3). I didn't experience this problem until a value was being posted to the table that didn't fit the (11, 3) range.

This issue highlights one of my biggest complaints with SSIS. On occasion I get errors that are often well documented on the internet. I search through all of my logs and I try to set up various test scenarios under the assumption that the error message is honest. Yet, when I finally solve the problem, it's completely unrelated to the error message that is written to the log file.

In this case, the error mentioned above had absolutely nothing to do with the problem?! In fact, I was very lucky to see the problem at all. I knew the update on my table might be a potential fix because I've seen SSIS mis-communicate like this before.

I'd like to blame this on neutrinos from space bombarding my server but the best take-away from this experience is to try and solve your SSIS problems based off of the advice of others, however, if their advice doesn't help, realize the issue may be unrelated to the SSIS error message and triple-check everything associated with the point of failure.

RLH
  • 15,230
  • 22
  • 98
  • 182
  • 3
    Wow..I would have never ever expected a data type issue to cause that issue. It still doesn't make any sense to me but I will keep this in mind. Thanks for sharing your find. – rvphx Oct 18 '12 at 15:22
  • rvphx: well, as I summarized, the take-away is not that a data-type missmatch was the cause of this *specific* error. Instead, it's the fact that SSIS is reporting an incorrect error. I don't know why, but this exact error always seems to be a "fall back" when something goes wrong in SSIS. There are couple of errors that I always check for, before I search the error long (i.e. duplicate key entry attempts also reports a bogus error.) This appears to be another one. :/ – RLH Oct 18 '12 at 15:58
5

I could not post the image in the comments, so posting it as an answer.

When you try to import the package to SQL Server, as soon as you right click and do "Import Package" you will get the following window.

enter image description here

Click on the rectangle box to the right of the window. It will give you the option of changing the protection level of the package. Change it to "Do Not Save Sensitive" and try running the package. A word of caution, this will require you to remove the existing package and re-import it again. So, you may want to try it on another machine before touching the existing configuration.

rvphx
  • 2,324
  • 6
  • 40
  • 69
  • Interesting. I see the Protection Level but I've never paid attention to it for two reason. First, it's blank. I would have initially assumed it would have inherited my initial settings, plus I believe I am overriding such a setting from the Job Execution task. Second, the field looks like it's disabled because of the grey background. I can change it, though. I am running another test right now. Once it finishes, and if it fails, I'll respond with the results of this update. That could take another day. This package takes a long time to run and I might be out of the office when it finishes. – RLH Oct 17 '12 at 18:31
  • Thank you for your help rvphx. Unfortunately this wasn't the solution to my troubles, so I can't give you the answer mark (but I can give you an upvote.) See my Answer for my fix which I wish was a little more useful to the community. – RLH Oct 18 '12 at 14:17
4

For me it was just the password for a connection Manager in SSIS, which was not saved. Input the password -> OK-> close dtsx file & reopen. Error gone.

Vijay
  • 41
  • 1
3

Ran into the same error message in SQL Server 2012. For me, restarting SQL Server Management Studio resolved the problem. Issue was likely caused by my domain password changing a few days ago while SSMS was open. If you run into a similar problem and it doesn't go away with a simple restart, make sure you check Control Panel\User Accounts\Credential Manager for cached account info and/or try a reboot.

Ram
  • 3,092
  • 10
  • 40
  • 56
GrayDwarf
  • 2,469
  • 2
  • 20
  • 22
  • I was getting the error message reported at the beginning of this post, too. My problem was a Connection Manager where the database security had changed and DTS could no longer connect with the specified credentials. UGH, SSIS! – RickC Feb 26 '16 at 12:58
1

Open the package dtsx then go to properties set ProtectionLevel EncryptSensitiveWithPassword and put password . Connect the package form sql sever job with same password.

See http://support.microsoft.com/kb/918760 and http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

Shahdat
  • 5,343
  • 4
  • 37
  • 37
0

This is a workaround to avoid the issue in the first place of saving sensitive passwords in the package.

I removed the FTP task and used a File System task instead to copied the file to the same location via a network share on the ftp server and avoided using the ftp protocol.

I was also able to save the package to the file system instead of the sql server store and it ran fine.

Hopefully it helps you!

Hiram
  • 409
  • 1
  • 4
  • 13