none
Getting error as "An exception occurred while launching the package from sql server"

    Question

  • Hello,

    We have an SSIS package which was in SQL Server 2012 imported in MSDB. We are calling it from c# code. Now we upgraded SQL Server 2012 to SQL Server 2016 and  imported SSIS Package in MSDB. When we are calling it from our .net application, we getting error as "An exception occurred while launching the package from sql server". 

    We are using same account which have enough permission in SQL Server. We tried various solution but still getting same issue. 

    Please help.

    Thanks,

    Thursday, May 23, 2019 2:33 PM

All replies

  • Hi Rajesh Lohar,

    Starting with SQL Server 2012 and later, native SSIS Deployment Mode is into SSISDB Catalog.

    It has long list of benefits, many ways of launching SSIS packages, including launching SSIS packages via stored procedures, SSMS, c#, PowerShell, etc. Please take a look here:

    Run an SSIS package with C# code in a .NET app
    Thursday, May 23, 2019 2:50 PM
  • Hi Rajesh, 

    We tried various solution but still getting same issue. 

    Could you please tell us what solutions you've tried? And please post the exact error messages. 

    You can try to run the package using DTExec or Agent Job

    If it works, there might be something wrong with your .NET APP, then please share the code, so that we can help you troubleshoot the problem. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 24, 2019 1:30 AM
  • Hello Yang/Yitzhak,

    Thanks for the reply. 

    I tried by importing SSIS package again. Also changed service account used to execute package. 

    Below is the screenshot of error :

    

    Friday, May 24, 2019 7:21 AM
  • Hi Rajesh, 

    The specified package could not be loaded from the sql server database

    Have you ever tried to run the package from Agent Jobs?

    Is that the same error?


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 24, 2019 10:13 AM
  • Hi Yang,

    I did not try to run the package from Agent Job yet. I will try and let you know.

    Thanks,

    Friday, May 24, 2019 12:13 PM
  • Hi Yang,

    I tried to run the package from Agent Job and it is running with Agent Job, but not running while calling from c# application.

    Thanks,

    Friday, May 24, 2019 2:01 PM
  • Hi Rajesh,

    • Please confirm that you deployed your SSIS project to the SSISDB Catalog.
    • The link to the official MS documentation was provided earlier:
      Run an SSIS package with C# code in a .NET app
      Please confirm that your source code is matching that.


    • Edited by Yitzhak Khabinsky Friday, May 24, 2019 3:08 PM
    • Proposed as answer by Yang.Z Monday, May 27, 2019 1:32 AM
    Friday, May 24, 2019 3:08 PM
  • Thanks Yitzhak,

    We did not deploy SSIS to SSISDB. We imported SSIS Package to MSDB of integration services and calling them from c#.

    Thanks,

    Monday, May 27, 2019 11:22 AM
  • Hi Rajesh,

    1. SSIS 2005, 2008, and 2008 R2 versions run-time environment was file system or MSDB database
    2. Starting with SSIS 2012 and later, i.e. 2014, 2016, 2017, and 2019 everything is changed. SSIS is a server-side product which requires SQL Server DB instance installation. It has its SSISDB Catalog, stored procedures, views, logging, reporting, management jobs, packages versioning, parameters, environments, etc.

    So it is strongly recommended to deploy your SSIS project to the SSISDB Catalog. After that just follow the official Microsoft documentation via links provided earlier in the thread, and everything will work for you.

    Monday, May 27, 2019 12:50 PM
  • Thank you Yitzhak,

    is there any way to this problem fix this problem so that we do not have to change our c# code and we can use MSDB to store and call package from there.

    Thanks,


    Tuesday, May 28, 2019 8:59 AM
  • Hi Rajesh, 

    You can also try to call the job from C#

    I tried to run the package from Agent Job and it is running with Agent Job, but not running while calling from c# application.

    It seems that the problem is with your C# application. 

    Please share the sample code, without sensitive information, which failed to call the package, so that we can help you troubleshoot the problem. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 28, 2019 9:08 AM
  • Hi Yang,

    Below is the c# code :

                                

    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

                    // Open the package
                    using (Package package = app.LoadFromSqlServer(
                        string.Format("{0}{1}", @"\\", definition.SSISPackageName),
                        definition.SqlServerName, null, null, null))
                    {
                        foreach (KeyValuePair<string, string> pair in definition.Parameters)
                        {
                            try
                            {
                                package.Variables[pair.Key].Value = pair.Value;
                            }
                            catch (Exception ex)
                            {

                            }
                        }
                        // Execute it
                        DTSExecResult result = package.Execute();
                        if (result == DTSExecResult.Failure)
                        {
                            Logger.Write("Package FAILED", string.Format("Package {0} FAILED execution.  Result: {1}", definition.SSISPackageName, result.ToString()), logCategory, System.Diagnostics.TraceEventType.Information, 0, 3);
                        }

                    }

    Thanks,

    Tuesday, May 28, 2019 1:56 PM
  • Hi Rajesh,

    Your c# code seems ok.

    Please make sure the following:

    • in VS that the SSIS package TargetServerVersion setting is 'SQL Server 2016'.
      Please refer to the screen shot below.
    • Assembly reference in C#, Microsoft.SqlServer.ManagedDTS.dll, is also from the SQL Server 2016.



    Tuesday, May 28, 2019 2:19 PM
  • Thanks Yitzhak,

    I followed the same what you mentioned. 

    1. I have VS 2013 so I did not find "TargetServerVersion" in the settings of SSIS project.

    2. I updated assembly references.

    But still I am getting same error.

    Thanks,

    Wednesday, May 29, 2019 10:32 AM
  • Hi Rajesh,

    You would need to use VS2015 or later, with the matching SSDT.

    Otherwise, your SSIS project and its packages are incompatible with your run-time environment, i.e. SQL Server 2016.

    Wednesday, May 29, 2019 4:02 PM