Recently I was debugging a issue which many of you might have faced and even may have fixed but still I thought to share it on an open portal so in case if someone is still looking for solution or yet stuck in this then he can get benefited from this piece.
Issue observed was that the a SSIS package was working fine in business development studio but the same package was not working via SQL agent job and throwing misleading message pointing towards connection issue. In case if package has connection to other databases like DB2, Oracle or MYSQL then the situation becomes more worse and confusing.
Below is the sample of the error message,
******************************************************************
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
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E14 Description: "An internal network library error has occurred. A network level syntax error has occurred.".
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "XXXXXXXX" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
******************************************************************
Here in this error message SQL is trying to point that there is some issue with connection but unable to tell exactly where to check and even none of the blogs I visited were even roaming around this. Many blogs suggested that I should change executing user to SQL proxy with appropriate permission etc etc but no exact help or fix provided.
While debugging with my colleague we started digging this up and after the long hunt we found a solution which made my day. Basically when we define a connection via connection manager in package and SSIS package would use it for execution but when we point the same SSIS package through SQL jobs it shows us multiple tabs in job step among which we have to click on the "Data Sources" tab and put the correct password in our desired connection string and save the changes.
In case if you again wish to make even a small change to same job without touching SSIS package step, again put the password and save it because once you edit the step and save it SQL agent hides the password and remains invisible to users. If we again click on save even without making any changes to job step or package, password disappears and users have to put that password manually.
This sounds something strange but this is for the security so any random user having access to sql jobs does not see password easily and password remains safe.
Hope this fixes your issues.
Please try this and share the feedback whether this fixed your issue or not of if something else needs to be added in this to make it more easy to understand.
