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
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
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.
User contributions licensed under CC BY-SA 3.0