SFTP by ssis
-
16. února 2012 17:10
Do anybody know how to go SFTP in ssis.
Thanks
Všechny reakce
-
16. února 2012 18:19Moderátor
-
16. února 2012 19:38
You can try a free command line utility named MOVEit Freely. I use it from SSIS to upload files to a secure FTP server
http://www.ipswitchft.com/products/moveitFreely/index.aspx
Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you -
16. února 2012 20:10
Is MOVEit Freely, a scripting language like WINscp.
Can you tell me how we can use it in ssis for SFTP.
Thanks
-
16. února 2012 20:35Moderátor
WinSCP is not a language but rather a mechanism (piece of software) controlled externally with a script as say DOS / VBScript.
You will the basic knowledge to use those but if not your best choice is items 2 or 3
Arthur My Blog
-
16. února 2012 20:40To Use MOVEit Freely you have to create a command line batch file with all the necesary commands and call this file from SSIS
Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you -
16. února 2012 21:19
Arthur
I am trying to install SFTP from codeplex as i think this will be the easiest option. When i try to run it, it is opening command prompt and says "gacutil.exe is not recognized as an internal or external command".
Please advice.
Thanks
-
16. února 2012 21:20
Continued...
Actually i was reading an article about installing SFTP but couldnt fully understand :
Thats what the article says:
path "C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin"
gacutil /uf "SSISSFTPTask100"
del "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\SSISSFTPTask100.dll"
copy $(TargetPath) "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\"
gacutil -i $(TargetPath)What that means? You have to copy the dll in “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\” folder for 64bits operation systems or “C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\” for 32bits operation systems and then you have to register it into GAC, so GAC it!
-
16. února 2012 21:21Moderátor You'll need to download and install the Visual Studio 2005/8 SDK. Included in the SDK is gacutil.exe. In order for Windows to recognise the gacutil command, you'll also need to modify the Path variable (Environment Varabiables). Simply add the location of the executable which is normally:
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
Remember to include a semi-colon before adding the folder location.Arthur My Blog
- Označen jako odpověď Harry Jaj 16. února 2012 21:26
-
16. února 2012 21:27Moderátor
The steps below are for a manual installContinued...
Actually i was reading an article about installing SFTP but couldnt fully understand :
Thats what the article says:
path "C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin"
gacutil /uf "SSISSFTPTask100"
del "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\SSISSFTPTask100.dll"
copy $(TargetPath) "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\"
gacutil -i $(TargetPath)What that means? You have to copy the dll in “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\” folder for 64bits operation systems or “C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\” for 32bits operation systems and then you have to register it into GAC, so GAC it!
• The DLL must be copied to the C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\ folder. For 64-bit systems, you will also need to copy the DLL to the C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Task\ folder.
• Register the .dll into GACArthur My Blog
-
16. února 2012 21:31
Iam already having visual studio 2008 (BIDS) running, so this visual studio 2005/8 SDK will be installed seprately upon it.
Will it affect my already developed packages in Visual studio 2008. I think i am sounding silly but want to get some knowledge on this.
Thanks
-
16. února 2012 21:35Moderátor
Will not affect. But probably no need for it then, did you try the manual install or the BAT file provided?
Arthur My Blog
-
16. února 2012 21:42
What you mean by manual install?
I am trying option 2 suggested by you.
I want to try and install SFTP from codeplex.I dont know much about creating batch file in WINscp.
Thanks
-
16. února 2012 21:45Can you tell me where i will get dll.
-
16. února 2012 21:48Moderátor
To install the CodePlex SFTP component you can use either do the manual procedure I showed above or using a BAT file provided in the download.
The bat file may need to be modified to suit your file system paths.
If the error about GACUtil IS happing after you followed either procedure then you need the SDK of version 2008.
Arthur My Blog
-
16. února 2012 21:52Moderátor
Can you tell me where i will get dll.
The SSISSFTPTask100.DLL is coming together with the Install_SQL_2008.cmd file.
The paths \Microsoft SDKs\Windows\v6.0A\Bin\x64 or \Microsoft SQL Server\100\DTS\Tasks\ may need to be revised to match those on your machine.
Arthur My Blog
-
16. února 2012 21:54
I want to go by manual method, for that how i can get DLL to copy to the said location.
-
16. února 2012 21:56
Till now, i just went straight to dowmload the SFTP from codeplex.
I was running the "Install_SQL_2008.cmd" which i got from codeplex.
-
16. února 2012 21:59ModerátorTo place the DLL you need the path, did you locate it (Program Files\Microsoft SQL Server\100\DTS\Tasks\ folder or Program Files (x86)\Microsoft SQL Server\100\DTS\Task)?
Arthur My Blog
-
16. února 2012 22:08
i am seeing a bunch of .Dll files in both locations that you mentioned:
C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\ folder (wHAT IS THIS FOLDER AT THE END,CANT FIND THAT ??)
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Task)
There is an "EN" folder and .dll files in both locations after .....\DTS\Tasks
-
16. února 2012 22:10
I thinks i am getting you now. This is same as TNS aura file i guess which oracle client interacts with.
But how i can get .dll file for SFTP.
-
16. února 2012 22:18
Yes Arthur, the 'SSISSFTPTask100.DLL' is in place at
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Task
So, what is the next step.
-
17. února 2012 16:07Moderátor
Register in GAC is the next step (when you are in the directory in DOS prompt issue:
gacutil /i SSISSFTPTask100.DLL
Arthur My Blog
-
20. února 2012 6:05
do i need to add environment variable after installing SDK
Should i add the location in user variable or system variable?
-
20. února 2012 6:26
If i am not wrong, i have to add
{;C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin}
in the system variable ('PATH' variable name)
-
20. února 2012 17:27
Hi ARTHUR
I have installed visual studio 2008 SDK and added the environment variable in the systen variable (in 'PATH')
like ;C:\Program Files(x86)\Microsoft Visual Studio 2008 SDK
and SSISSFTPTask100.dll is copied to C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks
Now when i try to open Install_SQL_2008.cmd, it open up command prompt and message is like:
"The system cannnot find the file specified.
Assembly successfully added to the cache.
The SQL server integration services was started successfully.
Press any key to continue......"
When i press any key, the command prompt closes. Can you help me with this.
Thanks
-
20. února 2012 17:33
Initially i was getting like
gacutil.exe is not recognized internal or external command
I am not getting it right now.
-
20. února 2012 17:57
I am getting somewhat near to what i want.
Now i can see the SFTP in the ssis control flow item. I checked that task. I closed the BIDS and reopen it but couldnt find SFTP in toolbox item.
-
20. února 2012 18:24
I am finally getting SFTP task in control flow
When i run it in package to send file to remote location, i am getting the following error:
[SSISSFTTask] Error: Error : timeout: socket is not established. Please check your username, password or port
I am wondering if it has to do something with gacutil.exe bcoz the username, password are correct and port is 22.
-
21. února 2012 14:52Moderátor
First sorry for the long wait, I was not in.
Second, congratulations, you have made significant progress
Third, the error is not related to the gacutil.exe because its purpose was just to let BIDS pick the SFTP task up.
The error you are facing is likely due to an incorrect configuration and/or slow response from the remote site.
To rule those out, try first establishing a test connection using a graphical user interface tool other than the SFTP task, e.g. WinSCP.
Arthur My Blog
- Označen jako odpověď Harry Jaj 21. února 2012 18:37
-
21. února 2012 18:37Thanks Arthur. It is working now. Thanks you your help.
-
13. června 2012 23:51
I like codeplex solution but we are still running SSIS 2005 on Visual Studio 2005. Has anyone gotten codeplex to work in Visual Studio 2005. I know the requirements say Visual Studio 2008 and Microsoft SQL Server 2008. But I just wanted to see if anyone got it working with 2005, because it seems to be the simpler option when it comes to freeware WinSCP and codeplex. Or does anyone know of a simple solution like codeplex I can implement using SSIS 2005?
Thanks
Scott
-
15. června 2012 18:38Moderátor
Scott, you cannot use SFTP task from CodePlex to use in 2005 SSIS packages, what you can, take the source code and compile it in VS 2005 or higher targeting SSIS 2005 (and .Net 2 runtime), then you can start using it in SSIS 2005.
PS: And then please make it shared on CodePlex, too. That would be helpful.
Arthur My Blog
.png)
