Run an FTP package from stored Procedure

  • Hi,

     I am trying to run a simple FTP package from stored Procedure.
    So if I run the package in SSIS it works, that is it moves a file to a server; no problem.
     run the code below and it does not work, but returns in Messages 31089. Not sure if that means anything?
    Any thoughts as to what I am doing wrong?
    Thank you


    create procedure [dbo].[execute_ssis_package_sample]
     @output_execution_id bigint output
    as
    begin
     declare @execution_id bigint
     exec ssisdb.catalog.create_execution
      @folder_name = 'NightlyPartnerPortalMaintenance'
     ,@project_name = 'TestFTP'
     ,@package_name = 'FTP_Test.dtsx'
     ,@execution_id = @execution_id output
     exec ssisdb.catalog.start_execution @execution_id
     set @output_execution_id = @execution_id
    end
    GO

    And to run it I am doing the following:


    declare @output_execution_id bigint
    exec dbo.execute_ssis_package_sample @output_execution_id output
    print @output_execution_id

  • itmasterw 60042 - Wednesday, February 21, 2018 8:36 AM

    Hi,

     I am trying to run a simple FTP package from stored Procedure.
    So if I run the package in SSIS it works, that is it moves a file to a server; no problem.
     run the code below and it does not work, but returns in Messages 31089. Not sure if that means anything?
    Any thoughts as to what I am doing wrong?
    Thank you


    create procedure [dbo].[execute_ssis_package_sample]
     @output_execution_id bigint output
    as
    begin
     declare @execution_id bigint
     exec ssisdb.catalog.create_execution
      @folder_name = 'NightlyPartnerPortalMaintenance'
     ,@project_name = 'TestFTP'
     ,@package_name = 'FTP_Test.dtsx'
     ,@execution_id = @execution_id output
     exec ssisdb.catalog.start_execution @execution_id
     set @output_execution_id = @execution_id
    end
    GO

    And to run it I am doing the following:


    declare @output_execution_id bigint
    exec dbo.execute_ssis_package_sample @output_execution_id output
    print @output_execution_id

    I haven't tried this way, But I'd tried thru command line.

  • Thanks for the reply.

    Well the problem is that I am ultimately going to run this from a VB.Net or c# application and it is easy to run a stored proc there. I am not sure how you would do a command line there.
    o I really need someone to tell me why this is not working.

    Thank you

  • On it's own, error message 31089 doesn't mean a lot. As you're running this in the SSISDB, the logs will tell you far more information. Have a look at these and reply back with the details you're seeing.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Hi,
     I tried running it directly on the server and there is in formed me that our Firewall was not allowing it, and asked me if I wanted to allow this I said yes and it now works.
    Thank you

  • itmasterw 60042 - Wednesday, February 21, 2018 10:32 AM

    Hi,
     I tried running it directly on the server and there is in formed me that our Firewall was not allowing it, and asked me if I wanted to allow this I said yes and it now works.
    Thank you

    So firewall restriction was the culprit, Noted. Next time similar issues come, firstly we can check the firewall settings. Happy learning !!🙂🙂

  • Have a look at the WinSCP .NET Assembly. You can then run FTP or SFTP direct from VB.NET or C#.

    Not how you want to use it but, I set up an SSIS wrapper around the assembly and deployed it as a generic SSIS package. I then call it when I need to in an SQL Agent Job step where I set the parameters according to the job at hand. We transfer 1000,s of files this way, it works like a charm.I must have about 15 jobs set up now that using it and adding more regularly.

Viewing 7 posts - 1 through 6 (of 6 total)

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