Post reply

SSIS Secure FTP Task

  • November 25, 2008 at 9:25 am

    #903238

    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!

  • November 25, 2008 at 9:28 am

    #903243

    Check your share permissions.

  • December 20, 2008 at 1:16 am

    #915718

    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

  • January 27, 2009 at 1:11 pm

    #933469

    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.

  • January 27, 2009 at 1:28 pm

    #933475

    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!

  • April 2, 2009 at 1:24 am

    #970409

    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.

  • June 23, 2009 at 7:21 am

    #1013899

    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!

  • December 3, 2009 at 2:08 pm

    #1087375

    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

  • February 1, 2010 at 2:19 pm

    #1111954

    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".

  • February 1, 2010 at 3:27 pm

    #1111985

    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/

  • February 2, 2010 at 9:57 am

    #1112379

    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?

  • February 2, 2010 at 10:00 am

    #1112384

    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/

  • February 2, 2010 at 1:01 pm

    #1112499

    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?

  • February 2, 2010 at 1:08 pm

    #1112504

    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/

  • February 4, 2010 at 8:49 am

    #1113802

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

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

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