I created a simple SSIS package to copy a table from one database to another. The source and destination connections used SQL Server authentication.
When I tried to run the package in an SQL Agent job I received an error message: [DTS.Pipeline] Error: component "Source - History" (1) failed validation and returned error code 0xC020801C.
Here was the fix for me. When I imported the SSIS package to Integration Services, I set the Package Protection Level to “Rely on server storage and roles for access control.”
These links helped in solving the problem:
I tried executing the following Bulk Insert command, having UNC paths, from SQL Server Management Studio running on my laptop and connected to the appropriate database. I connected using Windows Authentication.
BULK INSERT dbo.Load_itemDetail
WITH (FORMATFILE = '\\server1\Outbound\FormatFiles\ORDITEMNEW.FMT')
I then received the following error message:
Msg 4860, Level 16, State 1, Line 2 Cannot bulk load. The file "\\server1\Outbound\orditem.txt" does not exist.
When I had our administrator run the bulk insert command from within SQL Server Management Studio running on the same machine as the SQL Server instance, the command worked.
Fix: When running SQL Server Management Studio from a machine other than the machine where the SQL Server instance resides, you need to connect to the server where the database resides using SQL Server authentication. The SQL Server login account that you use to connect must also have the "bulkadmin" server role assigned to it. After logging in with SQL Server authentication, I was able to execute the Bulk Insert command without error.