SSIS Secure FTP Task

Question

SSIS Secure FTP Task

Viewing 15 posts - 31 through 45 (of 46 total)

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

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 1

Eureka! I finally found my problem. Would you believe that you can't run a .bat with a UNC path? This is a big pain in the butt because if I develop in BIDS on my PC, clearly I can't put E:\ (the server's drive location) and do any kind of testing, since my E:\ is my DVD drive. So, I was trying to use the UNC path. I found this post on another thread where a guy talks about getting around that by editing the registry, but I'm not brave (or possibly insane) enough to try that! http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1218443170

Anyway, in my Execute Process Task, I had browsed to the location of my executable and then changed it to the UNC path. That's what was causing it to blow up. It's the weirdest thing.

This is my location on the server:

E:\PowerCampus\XEI\FTP.bat

This is the UNC path:

\\servername\powercampus$\XEI\FTP.bat

If I'm logged on as the SQL Server Agent account and I double-click the http://FTP.bat the first way, it works beautifully. If I browse to it the second way, it tells me I don't have permissions. SO WEIRD!

Maybe this will help somebody else!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442467

November 25, 2008 at 9:28 am

#903243

Check your share permissions.

mayevski SSC Enthusiast Points: 104 More actions · Answer 3

Check SFTP connection and task for SSIS and FTPS connection and task for SSIS in SecureBlackbox. OpenPGP encryption of data is also possible using OpenPGP task for SSIS

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 4

please login as the user that starts the sql agent and run the exe or the bat file manully to store the key in the cache. So next time job runs will it will not hang

---------------------------------------------------------------------------------------
It begins by taking the first step.

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 5

Leju Geevarghese (1/27/2009)
please login as the user that starts the sql agent and run the exe or the bat file manully to store the key in the cache. So next time job runs will it will not hang

That's exactly what I did to fix it. Thanks!

kos SSC Enthusiast Points: 158 More actions · Answer 6

Seeing that many people are trying to use WinSCP to implement SSIS SFTP transfer task, I have prepared a guide to SFTP Task for SSIS.

See also WinSCP FAQ to learn why WinSCP (and PuTTY PSFTP) may hang when run from SSIS.

sfranzen Valued Member Points: 74 More actions · Answer 7

I'm trying to use PSFTP with SSIS as well and have the same problem. (Works fine when running the package manually - just hangs when scheduled) Has anyone ever figured this one out?? Thanks in advance!

MarcoAntonio SSC Veteran Points: 253 More actions · Answer 8

The instructions in this thread worked well for me, however I got burned by having special characters in the sftp site's password ... in particular the "%" sign.

When running within the batch file, I had to change each % into %%.

eg. if pwd is fn%%jn3, then in batch file need to store as fn%%%%jn3

ryan213 SSC Eights! Points: 937 More actions · Answer 9

CozyRoc (9/29/2008)
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.

I downloaded this (SSIS +1.4) and I've tried using the SFTP task. It just seems easier to use/configure. I'm trying to upload a file using this task and it works fine from BIDS, however, it's failing when trying to schedule it. Could it still be a permission issue with Agent even though I'm specifying the package to use my login to access the SFTP?

I'm getting this error:

Code: 0xC0014005 Source: Description: The connection type "SSH" specified for connection manager "SSH Connection Manager 1" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. End Error Error: 2010-02-01 16:04:26.88 Code: 0xC0010018 Source: Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">SSH Connection Manager 1</DTS:Property><DTS:Property DTS:Name="DTSID">{1B5BBC78-C2FC-4C97-A5B5-A" from node "DTS:ConnectionManager".

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

ryan213 (2/1/2010)
CozyRoc (9/29/2008)
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.
I downloaded this (SSIS +1.4) and I've tried using the SFTP task. It just seems easier to use/configure. I'm trying to upload a file using this task and it works fine from BIDS, however, it's failing when trying to schedule it. Could it still be a permission issue with Agent even though I'm specifying the package to use my login to access the SFTP?
I'm getting this error:
Code: 0xC0014005 Source: Description: The connection type "SSH" specified for connection manager "SSH Connection Manager 1" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. End Error Error: 2010-02-01 16:04:26.88 Code: 0xC0010018 Source: Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">SSH Connection Manager 1</DTS:Property><DTS:Property DTS:Name="DTSID">{1B5BBC78-C2FC-4C97-A5B5-A" from node "DTS:ConnectionManager".

Try to stop and restart your SSIS service. SSIS is doing some kind of component caching. If this doesn't work, restart your machine and try again.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

ryan213 SSC Eights! Points: 937 More actions · Answer 11

I restarted but it didn't work.

Do I need to install the CozyRoc software on the actual machine that's running the job? I develop the package on my machine and then save a copy on another machine. Does that matter?

COZYROC One Orange Chip Points: 28499 More actions · Answer 12

ryan213 (2/2/2010)
I restarted but it didn't work.
Do I need to install the CozyRoc software on the actual machine that's running the job? I develop the package on my machine and then save a copy on another machine. Does that matter?

Yes, you have to install CozyRoc SSIS+ on the machine doing the actual package execution.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

ryan213 SSC Eights! Points: 937 More actions · Answer 13

Will I need licensing for this? I've downloaded SSISPlus14.msi from the cozyroc site and that's what I've been using on my machine. Will I need some sort of license to be able to schedule packages using some of these features?

COZYROC One Orange Chip Points: 28499 More actions · Answer 14

ryan213 (2/2/2010)
Will I need licensing for this? I've downloaded SSISPlus14.msi from the cozyroc site and that's what I've been using on my machine. Will I need some sort of license to be able to schedule packages using some of these features?

When you test and develop from Visual Studio (BIDS), no license key is required. A CozyRoc license key is required if you want to schedule and execute packages under SQL Job Agent.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

ryan213 SSC Eights! Points: 937 More actions · Answer 15

Ah, well that's what the problem is! hehe Thanks for the help anyway!