I created a package for exporting data from one instance of SQL Server and putting it in a table in the local database. The package runs fine in the Export Wizard. I have tried saving it both to the file system and in SQL server. When I run it from an agent step it fails ever time.
In the SQL Server log the error is: Login failed for user 'myusername'. Reason: Password did not match that for the login provided.
When I save the DTS package I "encrypt sensitive data with a password" and I supply that password in the Agent Step Dialog.
In the Windows NT log I get:
Date 2/25/2016 3:59:55 PM
Log Windows NT (System)
Source Microsoft-Windows-DistributedCOM
Category (0)
Event 10016
User NT SERVICE\SQLSERVERAGENT
Computer BIserv01
Message
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.
I looked up the CLSID in the registry and found it was Microsoft.SqlServer.Dts.Server.DtsServer. I read that I needed to add permissions in the DCOM Config. In there I find DTS Package Host and DTS Task Host. I'm logging in to SQL server with an SA account and not Windows Authentication. So I'm not sure who I should be adding permissions for. I tried my Windows login, with no luck. I try adding NT SERVICE\SQLSERVERAGENT, but it is not found when I click on Check names.
I'm not really sure what is going on here. Any help is appreciated. I've recreated this a half dozen times. There is no way I am typing my password wrong ever time.
Greg