February 21, 2018 at 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
February 21, 2018 at 8:44 am
itmasterw 60042 - Wednesday, February 21, 2018 8:36 AMHi,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
GOAnd 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.
February 21, 2018 at 9:11 am
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
February 21, 2018 at 9:19 am
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
February 21, 2018 at 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
February 21, 2018 at 9:28 pm
itmasterw 60042 - Wednesday, February 21, 2018 10:32 AMHi,
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 !!🙂🙂
February 26, 2018 at 9:52 am
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