Problem running a SSIS package

itms

Active member
Joined
Feb 21, 2018
Messages
33
Programming Experience
10+
Hi,

I have a Vb.Net application that when the user hits a button it runs a SQL Server stored procedure. that executes a SSIS package on the server.
In this package it gets the data from a table on the server, then moves and renames the file to a staging table. It then moves the to the folder on a another server. I am using the SSIS package because after this step it is going to FTP it to another server.
the strange thing is if I run the package in SSI it works great.
If I execute the package on the server it works great.
If I execute the stored procedure it works great.
However, if I run the VB app it run through all but the last step in the package and stops. No error, just does not work
Any ideas why and what I can do ?

Thank you
 
Can you please post the code snippets for the vb.net app calling the sql server stored procedure and the stored procedure initiating the SSIS package?
 
Can you please post the code snippets for the vb.net app calling the sql server stored procedure and the stored procedure initiating the SSIS package?


Here is my code and the stored procedure for running the SSIS package, but please remember that if I run this procedure alone it works and if I run the VB it works only up to the last step; and all other ways it works. That is if I execute the package, the procedure, or if I run it in SSIS; it fully works.
I even tried putting a 20 second delay in the code before it closes the connection and that did nothing.

Thank you

The Vb Code

VB.NET:
Dim dts As New DataUtils
        Dim cnst2 As String = dts.SQLbConnect
        Dim cn2 As SqlConnection = New SqlConnection(cnst2)
        cn2.Open()
        Try
            Dim command As SqlCommand = New SqlCommand("uspSisFtpPackageInteractiveData", cn2) 'Sets the procedure to the command.
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@output_execution_id", "") 'Sets the parameter, which in this case, is null because it is an out put parmmeter.
            command.ExecuteNonQuery().ToString()
            'Threading.Thread.Sleep(10000) ' 10 seconds
            cn2.Close()
            cnt = MessageBox.Show("The file has been successfully sent to the FTP site", "File Check", MessageBoxButtons.OK, MessageBoxIcon.Hand)
            Me.Close()
        Catch ex As Exception
            'If an error occured alert the user
            txtErrorMesg.Visible = True
            txtErrorMesg.Text = "Error: " + ex.Message
        End Try

Stored proc to run the Package:

VB.NET:
CREATE procedure [dbo].[uspSisFtpPackageInteractiveData]
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'SSIS_Misc_Projects'
 ,@project_name = 'FTP_DMPInteractveData2'
 ,@package_name = 'FTP_DMPInteractveData.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end
 
O, everything looks as I thought it would, so now I'm wondering what user account(s) those things run under when it's initiated from your machine and what user account(s) they run under when initiated from the vb app (as well as if the vb app runs as a different user than your machine)?
 
Back
Top