John Stagich's Blog

Microsoft .Net Developer

SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

clock February 16, 2010 09:46 by author JohnStagich

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: 
 

http://support.microsoft.com/kb/918760
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsprotectionlevel.aspx
http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

 

 

 

 



Bulk Insert Error with UNC Path: Message 4860

clock February 2, 2010 11:36 by author johnstagich

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
FROM '\\server1\Outbound\orditem.txt'
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.

 



About the author

I am the owner of Stagich Software Consulting.  Stagich Software Consulting specializes in developing Microsoft .Net/Microsoft SQL software applications.

 

Calendar

<<  November 2024  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

Month List

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Sign In