SSIS Secure FTP Task
-
Check CozyRoc's SSIS+ library, if you want straightforward solution. It is your choice. You either have time on your hands or you want to get the job done ASAP.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi halexander,
I tried your suggesstion and it works perfectly when I run my package from BIDS or Mgt. Studio (deployed). However, I can't seem to make it work once I run the package in sql agent as a job. I'm getting the following error:
The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:48:33 AM Finished: 5:48:55 AM Elapsed: 21.812 seconds. The package execution failed. The step failed.
Again, without modifying anything, I tried to run the package from Mgt. Studio or BIDS and it works. Do you think this is a permissions issue? Perhaps on my script file (where the WinSCP commands are found)?
Thanks.
Jafar
-
I experienced a similar issue, although I was still running DTS on SQL 2005. The resolution was to grant the SQL agent user create/update permissions to the WinSCP folder. It appeared the process deleted and recreated the WinSCP.ini file.
-
I have given the sql agent Full Control to the WinSCP folder, still no resolution yet. Any help is greatly appreciated, thanks!
-
I'm having this same problem. It runs fine from BIDS or even directly from SSMS when connected to Integration Services on the server. However, when I schedule it, it just runs indefinitely. I need help, please! It took me a week to get the Network Admin to even install PuTTY. If this won't work, what's the fastest thing I can do? It has to be a secure FTP protocol. Thank you.
-
The fastest thing you can do is use SFTP Task.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
You're not alone. I'm having the same issue. My SSIS package runs fine in Visual Studio but when I schedule the package as a SQL Server Agent job it hangs on the task that executes a batch file that contains a command with the username and password parameters to run the PSFTP executable. I just posted a message about it on the forum last night.
-
Have your tried logging into the the server under the SQL Server agent account and running the PSFTP install there, so that the registry is updated with the program values under that account? I used to have the same problem, but once I actually installed the program under the user account that my SQL Server agent runs as, the problem went away and now things run without issues.
Just a suggestion...
-
mpothier (11/20/2008)
Have your tried logging into the the server under the SQL Server agent account and running the PSFTP install there, so that the registry is updated with the program values under that account? I used to have the same problem, but once I actually installed the program under the user account that my SQL Server agent runs as, the problem went away and now things run without issues.Just a suggestion...
Agree with you,I had not worked on any SFTP tools but had the similar problem when I was using PGP to perform encryption.I checked the accounts that the sql agent service run under and executed the required PGP files under that account and ran my job.My problem was solved.
Maybe you can try it out as well for SFTP.
-
If you are using psftp, there is a feature that will prompt you the first time you run the software to put the server's key in your cache. This prompt is user specific, so it may run fine under your login but will wait indefinitely when you run it under your SQL Agent account.
To get past this, you can add the -batch switch when you call the executable that will suppress interactive prompts.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
Everyone,
Thank you for your hints. I'm still having trouble. I logged onto the server under the SQL Server agent account and ran psftp from the command line prompt, answered Y when it asked about accepting the key, etc... I then ran my package from the scheduled jobs area. It still failed. This is the error message:
Date11/24/2008 8:06:50 AM
LogJob History (TNU_XEI_Interface)
Step ID1
ServerASSHUR
Job NameTNU_XEI_Interface
Step NameRun SSIS package
Duration00:18:49
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: GARDEN\sqlpcadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:06:50 AM Error: 2008-11-24 08:06:51.49 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-11-24 08:25:39.68 Code: 0xC002F304 Source: FTP files Execute Process Task Description: An error occurred with the following error message: "Access is denied". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:06:50 AM Finished: 8:25:39 AM Elapsed: 1128.77 seconds. The package execution failed. The step failed.
-
This doesn't look like a problem with PSFTP, but hat you have the encrypted (saved sensitive information) the package you are trying to run on your server.
-
Lynn,
You're right. My problem was that I developed the package in BIDS and saved it using the "encrypt sensitive data with user key." Then when I imported it back in, I guess it didn't allow the SQL Server agent account to have the proper access. I got that part fixed, but now I'm still getting an acess denied error when it gets to that cmd line task.
If I log onto the server with the SQL Server agent account and I go to a cmd prompt and call my *.bat file, it works beautifully. I just cannot get it to work through the scheduled job area. Any other ideas?
Thank you all for your help so far. I'm desperate to get this to work. I was thinking of using xp_cmdshell and wrapping it in a stored proc and calling it that way, but I don't know if it will help.
-
Can you run the batch file as an OS step in a job?
-
Ok, so I wrapped the whole thing in a .bat and set it up as an "Execute Process Task" in SSIS. If I run it from BIDS, it works. If I run it as a scheduled task on the server (under the SQL Server agent account), it works! However, if I run it from inside SSMS connected to Integration Services, it fails. This is the error message.
9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:03:00 PM Error: 2008-11-24 16:21:23.91 Code: 0xC002F304 Source: FTP files Execute Process Task Description: An error occurred with the following error message: "Access is denied". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:03:00 PM Finished: 4:21:23 PM Elapsed: 1103.77 seconds. The package execution failed. The step failed.
I noticed that the version number it references is 9.00.3042.00. We are actually on 9.00.3068. I'm not sure if that's a clue about the cause of the issue or not. I'm so confused. I don't understand what "access" is being denied. The SQL Server Agent account has access to write to the file system, execute tasks, etc... and it can run that .bat file from scheduled tasks or while BIDS is open. What could the issue be? Anybody ??
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply