No SFTP Task Component in SSIS 2005/2008? No Problem!

Question

Post reply

No SFTP Task Component in SSIS 2005/2008? No Problem!

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Peter De Ceulaer SSC Enthusiast Points: 101 More actions · Answer 1

I've dropped the putty solution in favor of ssissftp.codeplex.com, a custom package that has worked very well for me.

angel_mata Grasshopper Points: 19 More actions · Answer 2

Hi Mark,

Great post on the C# script. I'm a complete newbie with this. Do you have a download version of the script?

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 3

I have just written an article on an SFTP open source custom task component that works much more reliably than this method:

"SFTP, encrypt or compress data files in SSIS using custom components"[/url]

khicon73 Old Hand Points: 300 More actions · Answer 4

I download the 'SftpScriptTaskDemo.dtsx', also download 'psftp.exe' and placed it in 'C:\psftp\psftp.exe', and imported into my SSIS project. I modified the SFTP's credentials...It run fine, no error but i didn't see any file upload to sftp site. what did i do wrong? Please help.

Thank you so much.

I know it's nothing wrong with the credentials because i can manual transfer the files to that server.

1st script i modifed below, and i haven't touch anything on 2nd script.

Public Sub Main()

'Load global variables with values

Dts.Variables("PathToBatch_bat").Value = "c:\psftp\psftp.bat"

Dts.Variables("PathToPsftp_exe").Value = "c:\psftp\psftp.exe"

Dts.Variables("PathToScript_sc").Value = "c:\psftp\script.sc"

Dts.Variables("ServerName").Value = "sftp.server_name.domain"

Dts.Variables("ServerUserName").Value = "username"

Dts.Variables("ServerPassword").Value = "password"

Dts.Variables("ServerSubdirectory").Value = "/sftp"

Dts.Variables("SourceSubdirectory").Value = "C:\psftp\Test.csv"

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

Please help, i'm very appreciated.

Thanks again.

vonda.ackerman SSC Journeyman Points: 75 More actions · Answer 5

I have had great success on using this many times. You may want to make the following change and try it.

Also make sure that you put your variables in the ReadWriteVaraibles on your script task.

Public Sub Main()

'Load global variables with values

Dts.Variables("PathToBatch_bat").Value = "c:\psftp\psftp.bat"

Dts.Variables("PathToPsftp_exe").Value = "c:\psftp\psftp.exe"

Dts.Variables("PathToScript_sc").Value = "c:\psftp\script.sc"

Dts.Variables("ServerName").Value = "sftp.server_name.domain"

Dts.Variables("ServerUserName").Value = "username"

Dts.Variables("ServerPassword").Value = "password"

Dts.Variables("ServerSubdirectory").Value = "/sftp" --change this to "sftp" (if root folder, just leave as ""

Dts.Variables("SourceSubdirectory").Value = "C:\psftp\Test.csv"

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 6

khicon73 (5/5/2015)
I download the 'SftpScriptTaskDemo.dtsx', also download 'psftp.exe' and placed it in 'C:\psftp\psftp.exe', and imported into my SSIS project. I modified the SFTP's credentials...It run fine, no error but i didn't see any file upload to sftp site. what did i do wrong? Please help.
Thank you so much.
I know it's nothing wrong with the credentials because i can manual transfer the files to that server.
1st script i modifed below, and i haven't touch anything on 2nd script.
Public Sub Main()
'Load global variables with values
Dts.Variables("PathToBatch_bat").Value = "c:\psftp\psftp.bat"
Dts.Variables("PathToPsftp_exe").Value = "c:\psftp\psftp.exe"
Dts.Variables("PathToScript_sc").Value = "c:\psftp\script.sc"
Dts.Variables("ServerName").Value = "sftp.server_name.domain"
Dts.Variables("ServerUserName").Value = "username"
Dts.Variables("ServerPassword").Value = "password"
Dts.Variables("ServerSubdirectory").Value = "/sftp"
Dts.Variables("SourceSubdirectory").Value = "C:\psftp\Test.csv"
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Please help, i'm very appreciated.
Thanks again.

I wouldn't do it this way any more.

I would either use the custom task component in this article I wrote:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/106616/

or I would use the WinSCP assembly referred to below:

http://winscp.net/eng/download.php

http://winscp.net/download/winscp556setup.exe

http://winscp.net/download/winscp556automation.zip

You can use the Global Assembly Cache Manager to install the assembly.

http://gacmanager.codeplex.com/

The WinSCP method is the most reliable and fail-safe. I am writing an article on how to do it, but it is not finished yet. You should be able to figure it out from the links.

khicon73 Old Hand Points: 300 More actions · Answer 7

Thanks Stan Kulp

I'll give it a try.

Very much appreciated.

TN

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 8

Here is a tutorial on using WinSCP to SFTP in SSIS:

http://www.sanjaykumar.us/index.php/automate-sftp-ssis-part-1-winscp-installation/

khicon73 Old Hand Points: 300 More actions · Answer 9

Hi,

I was able to create a package to uploads all *.csv files from local folder to sFTP using WINSCP. It runs fine in BIDS and Integration services manually. However, when i setup a job scheduler to automate, it's not working, it hang. It runs under a proxy account.

What do i need to do? Please help

Thank you so much.

TN

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 10

I would first create a batch (.bat) file from which you can run your SSIS package.

The batch file should contain the following command:

DTEXEC /File "C:\Path\MyPackage.dtsx" /CHECKPOINTING OFF /REPORTING D

Replace "C:\Path\MyPackage.dtsx" with the path to your SSIS package.

Open Notepad and paste the code into it, then save the file as "MyPackage.bat," or whatever name you want to give it.

Double-click the batch file from Windows Explorer to confirm that the package runs from the batch file.

Once you have it running from the batch file, just schedule the batch file to run from Task Scheduler.

You may need to add a configuration file to the command if you are using SQL Server connections in the package.

The command line would then look like this:

DTEXEC /File "C:\Path\MyPackage.dtsx" /Conf "C:\Path\MyPackage.dtsConfig" /CHECKPOINTING OFF /REPORTING D

Try it without the configuration file first. If it doesn't work, see if you can find a tutorial on the web that shows you how to create an SSIS package configuration file.

khicon73 Old Hand Points: 300 More actions · Answer 11

I just did create a batch file and double click to run, it runs just fine and upload file to sFTP. However, I still had error when scheduling a job to run.

Error:

Message

Executed as user: Domain\SQLAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Option "DTEXEC" is not valid. The command line parameters are invalid. The step failed.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 12

Try setting the password for the task as shown in this link:

http://myitforum.com/myitforumwp/2013/01/16/changing-the-password-for-windows-task-scheduler-tasks/

It would be the password you use to log into Windows.

khicon73 Old Hand Points: 300 More actions · Answer 13

khicon73

Old Hand

Points: 300

May 7, 2015 at 1:57 pm

#1796395

Did everything you suggested but no luck...;(

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 14

I don't really know what to do next.

Here are some links I found while searching for an answer. You might give them a try.

http://sql-articles.com/articles/dba/how-to-schedule-ssis-package/

https://technet.microsoft.com/en-us/library/dd440761(v=sql.100).aspx

http://stackoverflow.com/questions/846135/schedule-ssis-package-execution

http://blog.sqlauthority.com/2011/05/23/sql-server-running-ssis-package-in-scheduled-job/

https://social.technet.microsoft.com/Forums/sqlserver/en-US/2e9ab071-f82d-4e8d-9353-fe334ae3fc7c/launching-ssis-package-using-dtexecexe-with-the-task-scheduler?forum=sqlintegrationservices

khicon73 Old Hand Points: 300 More actions · Answer 15

Thank you so much for your time, I'm very appreciated. :-):-)

My works below:

I created 2 packages, both same method, run under proxy account.

Each package needs to export data from different tables, save to csv files and then upload to FTP and sFTP.

The one uploaded to ftp, i had no problem. It's completed and scheduled to run every 2 hours.:-P:-P:-P

The one needed to upload to sFTP, I had to download Winscp and then upload to sFTP. Everything run fine in BIDS and Integration manually, but failed in Job Scheduler.

If i scheduled a job to run directly from File System, job failed with error:

Message

Executed as user: Domain\AgentService. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:14:37 PM Error: 2015-05-07 16:14:40.09 Code: 0xC0029151 Source: EPT - Perform SFTP Using WinSCP COM Utility Execute Process Task Description: In Executing "C:\Program Files (x86)\WinSCP\WinSCP.exe" "-script="\\sqlserver1\c\SSISProject\MyScript.txt"" at "C:\Program Files (x86)\WinSCP", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:14:37 PM Finished: 4:14:40 PM Elapsed: 2.218 seconds. The package execution failed. The step failed.

If i created a batch file as you suggest, batch file run fine too.

Then I modified the command line, run with/without the configuration, failed again with error:

Message

Executed as user: Domain\AgentService. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Option "DTEXEC" is not valid. The command line parameters are invalid. The step failed.

Again, thanks

TN