LoadFromSQLServer method has encountered OLE DB

2

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 
sql-server
dtexec
asked on Stack Overflow Sep 6, 2012 by user1653113 • edited Oct 6, 2012 by Yaroslav

2 Answers

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 on Stack Overflow Sep 6, 2012 by Conrad Lotz
0

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"
answered on Stack Overflow May 10, 2018 by EzLo

User contributions licensed under CC BY-SA 3.0