none
SSIS and Secured FTP Commands to GET a Remote File using wildcards RRS feed

  • Question

  • So my biggest caveat here is dealing with wildcards! For the life of me I cannot find any good examples of SSIS and scripting that uses FTP wildcards to GET certain Files. In a nutshell, here's what I need to do...

    1. Query a SQL Server Database which has a parsed File Name, the first 50 characters of the file name.
    2. The Query "Result Set" is put into an Object Variable User::SQLServerFileList
    3. I then utilize a "Foreach Loop Container" which reads the User::SQLServerFileList and puts it into Variable User::SQLServerFileNm...which is again the first 50 characters of the File Name
    4. Within the "Foreach Loop Container", I then utilize an "Expression Task" which builds a variable User::RemoteFileLookup which is a concatenation of the User::RemoteFolderPath + User::SQLServerFileNm + the wildcard *(Variable User::RemoteWildCard)
    5. I then try and utilize a "FTP Task" to use that concatenated Variable to go and GET the Filename but every time I try, it does not like what I'm sending via the "FTP Task"
    Error: 0x0 at TF Secure FTP Task, ExecuteTask Failed:: Illegal characters in path.

    I realize I might have to do something like this via C#.

    My biggest challenge is providing the GET Command via the Remote FTP Site with a parsed Filename and utilizing a wildcard.

    mc7i1231_20140227_050114_27_05_02_09*.999

    And the Filename that exists on the FTP Server is...

    mc7i1231_20140227_050114_27_05_02_09_x12_a43419452ca844a9b8a00f61e655dca3.x12-20140303180032.999

    Can any gurus out there PLEASE help me out???

    Thanks in advance for your review and am hopeful for a reply.

    PSULionRP

    Wednesday, April 9, 2014 9:07 PM

Answers

  • Hi PSULionRP,

    According to the document FTP Task, we can read that:

    The FTP task supports the ? and * wildcard characters in paths. This lets the task access multiple files. However, you can use wildcard characters only in the part of the path that specifies the file name. For example, C:\MyDirectory\*.txt is a valid path, but C:\*\MyText.txt is not.

    So, when you use expression tobuild the variable RemoteFolderPath, make sure the evaluated value of the expression conforms to the above rule. 

    Regards,


    Mike Yin
    TechNet Community Support

    Friday, April 18, 2014 3:56 AM
    Moderator

All replies

  • I could be wrong since I've never used the FTP task in SQL, but if you're using any kind of command based FTP you'd need to be using mget rather than get in order to use wildcards.

    Get doesn't support downloading multiple files or wildcards, it's just designed to download a single specific file (obviously FTP programs let you select multiple files to download, but in the background they're issuing multiple get commands).

    I also believe "<string>*.<string>" wouldn't be valid. You need to have the * either at the beginning or the end of the string, you can't have it in the middle, at least definitely not with mget.

    Wednesday, April 9, 2014 9:33 PM
  • Check the commercial COZYROC SFTP Task. It does support download with wildcards.

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

    Thursday, April 10, 2014 2:00 AM
  • Hi PSULionRP,

    According to the document FTP Task, we can read that:

    The FTP task supports the ? and * wildcard characters in paths. This lets the task access multiple files. However, you can use wildcard characters only in the part of the path that specifies the file name. For example, C:\MyDirectory\*.txt is a valid path, but C:\*\MyText.txt is not.

    So, when you use expression tobuild the variable RemoteFolderPath, make sure the evaluated value of the expression conforms to the above rule. 

    Regards,


    Mike Yin
    TechNet Community Support

    Friday, April 18, 2014 3:56 AM
    Moderator