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

score 0 · Answer 1 · answered Sep 06 '12 at 20:55

0

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

answered Sep 06 '12 at 20:55

Conrad Lotz

8,200
3
23
27

Thanks But I found the issue it was a really stupid mistake but the error was falsely pointing to the permissions.The issue was that the instance had incorrect slash "hqgmdw02\dw_dev" and some quotes were wrong. The bottom line is DTEXEC can give misleading error messages.So focus on syntax and then actual error message. – user1653113 Sep 06 '12 at 21:17

score 0 · Answer 2 · answered May 10 '18 at 09:41

As the OP mentioned, the server routes (for parameter /SERVER) should use backslashes and not common slashes. The error being displayed is a little misleading for this occasion, as the login times out because the server path is incorrect.

So change

/SERVER "hqgmdw02/dw_dev"

for

/SERVER "hqgmdw02\dw_dev"

LoadFromSQLServer method has encountered OLE DB

2 Answers2