LoadFromSQLServer method has encountered OLE DB

Question

I have this error:

LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed

And here is my code, what is wrong?

DECLARE @FileName VARCHAR(50); 
DECLARE @VendorID VARCHAR(50); 
DECLARE @sql VARCHAR(2000); 
DECLARE @Local_File_FullPath VARCHAR(100);   

SET @FileName = 'Extgt_skinny_file.txt.pgp' 
Set @VendorID = 'ET' 

Select @Local_File_FullPath = dw03_path  FROM GMAC_META.dbo.VENDOR_XFER_METADATA where vendor_id = @VendorID 

SET @sql = 'dtexec /SQL "\EMAP_FTP_XFER_CHECK" /SET \Package.Variables[User::FileName].Properties[Value];"' 
+ @FileName+'" /SET \Package.Variables[Local_File_FullPath].Properties[Value];' 
+ @Local_File_FullPath+' /SERVER "hqgmdw02/dw_dev"   /CHECKPOINTING OFF /REPORTING E'


 exec xp_cmdshell @sql 

Answer 1

Try the following:

GRANT exec ON xp_cmdshell TO '<somelogin>'. Please refer to xp_cmdshell (Transact-SQL).

Check to see if you are using a 32-bit DTExec on a 64-bit machine.

Ensure that the users that will run the SSIS package have sufficient permissions. I take that you run is under SSIS in a SQL Server Agent or you might run it manually. The service account running SQL Agent and your account must have permissions to execute the job. Please see Error in executing SSIS package through Agent