July 6, 2013 at 9:04 pm
I did an SFTP to command the job via winSCP.
I manage to run execute it in SSIS.
When I try to schedule it in SQL Agent, I'm getting this error:
Code: 0xC002914C Source: Execute Process Task Execute Process Task Description: The process timed out. End Error DTExec: The package execution returned DTSER_FAILURE (1).
I did set password EncryptSensitiveWithPassword.
In the SQL agent job it manage to pump in the data to my local folder but will hit the error as above to SFTP over.
Execute in SSIS is successful.
How can I fix this?
July 7, 2013 at 3:42 pm
Maybe a permission issue with the SQL Agent account?
Did you use a proxy to run the SSIS job step?
You can try to log the details of the winscp command to a log file. Add > log.txt add the end of your command.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 8, 2013 at 5:57 am
At a guess, I would say that WinSCP is prompting for a password it never receives.
How are you logging into your target SFTP server? Are you using its in-built stored sessions to store a password (or private key)?
If so, the sessions are stored in a user-specific registry key (HKEY_CURRENT_USER\Software\Martin Prikryl\WinSCP 2\Sessions). This will not be the same for your user as for the user running your SQL Server Agent.
Two options I can think of (if this is indeed the issue):
1. Define a proxy account for the user that stored the session in WinSCP, and run the WinSCP step/job under that proxy account
2. Export the WinSCP user-specific registry keys and import them into the user hive for the user running your SQL Server Agent
For example, if your SQL Server Agent is running as NETWORK SERVICE:
1. Open REGEDIT
2. Navigate to the "HKEY_CURRENT_USER\Software\Martin Prikryl" key
3. Right-click the "WinSCP 2" key and choose Export - specify a filename
4. Open that file with a good text editor
5. Replace all instances on "HKEY_CURRENT_USER" with "HKEY_USERS\S-1-5-20" and save
6. In REGEDIT, go to File >> Import, and select your edited .reg file
Now NETWORK SERVICE should have the same saved sessions as your own user account.
(I should point out that manual fudging of the Registry, without knowing what you are doing and without a really good, thoroughly tested backup is a really bad idea -- make sure you test this on a non-essential test server, and have a good backup of your production first)
HTH
J.
July 8, 2013 at 8:15 pm
i hit error accessing the registry during importing.
July 9, 2013 at 6:52 am
girl_bj0619 (7/8/2013)
i hit error accessing the registry during importing.
The package failed with a registry-access error while you were performing an import?
What was the error message?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 9, 2013 at 7:36 pm
Cannot import. Error accessing the registry.
July 10, 2013 at 12:27 am
girl_bj0619 (7/9/2013)
Cannot import. Error accessing the registry.
You did not answer my first question and your error message was not the exact error message which you received.
If you provide only snippets of detail rather than the full story, it makes it very difficult for me to understand what is going on.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 10, 2013 at 12:35 am
I hit that error on after trying on steps number:
6. In REGEDIT, go to File >> Import, and select your edited .reg file
That's the error I get.
July 10, 2013 at 12:55 am
Right - sounds like you have limited rights on that PC. Can you find someone with local admin rights who can do it for you?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 10, 2013 at 2:57 pm
girl_bj0619 (7/9/2013)
Cannot import. Error accessing the registry.
If you don't have the OS privileges to globally edit your Registry, you'll need the assistance of your server administrators. If you can't get their assistance, you will have to go with the first option of running your job with a proxy account.
Of course, if you don't have the SQL Server privileges to create a proxy account, you're going to need to ask your DBA.
Otherwise, perhaps you can do your SFTP using an .NET Script Task, linking in a reputable SFTP library. http://winscp.net/eng/docs/library_ssis has an example. However, you may need to ask your server administrators to import the library into the central store for .NET assemblies (the GAC).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply