0

I created a package in Visual Studio 2015. It works fine.

Basically I am using a script task that generates Excel spreadsheet and sends it to different users.

After I deploy the package to SQL Server 2012 and then try to execute it from there - I get an error without any further details.

enter image description here

I also run select * from internal.packages from SSISDB to make sure package_format_version is 6, which is what should be for SQL Server 2012.

enter image description here

What could be the problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Serdia
  • 4,242
  • 22
  • 86
  • 159
  • Related: https://www.sqlservercentral.com/Forums/1820159/Exception-has-been-thrown-by-the-target-of-an-invocation-SSIS-Package-Integration-Service-Catalog?PageIndex=%5B0%5D – dfundako Oct 04 '18 at 16:24
  • Does the server have the libraries installed that you are using to create Excel spreadsheets? Does the account under which the package is run on the server have the permission to write or send files to relevant places? Do you have a better error message if you create an SQL Server Agent job that runs the package? – GSerg Oct 04 '18 at 16:25
  • Does the server have the libraries installed that you are using to create Excel spreadsheets? - I am not sure about libraries but I have agent jobs that also create Excel spreadsheets and sends via email. – Serdia Oct 04 '18 at 16:29
  • Does the account under which the package is run on the server have the permission to write or send files to relevant places? - Again, its my account and I have multiple agent jobs and also multiple SSRS reports that create excel files. – Serdia Oct 04 '18 at 16:30
  • I seem to remember having this issue when deploying an SSIS package containing a C# script. In our case, we had to use SSMS 2014 to deploy the package. But we were also going from 2008>2014, in your case I am not sure this would help going backwards. I hate to suggest you recreate in VS 2013 but that *might* be the solution. Hopefully you find another fix. – Jacob H Oct 04 '18 at 16:47
  • Have you tried moving back the targeted framework in your script task? Most of the time you don't need the most recent framework to accomplish your basic script tasks – KeithL Oct 04 '18 at 17:47
  • I just created simple project using SendMail task. After I changed TargetServerVersion to SQL Server 2012 - I was able to deploy it and execute it from SSMS. So, something with using C# code in script task – Serdia Oct 04 '18 at 18:43

2 Answers2

3

This necessarily isn't an answer on how to fix the issue, but it's an answer on how you can modify your script task to get a better error message then "Script Task Failure: Exception has been thrown..."

We'll always wrap our script tasks in a try-catch and then raise the exception message back out of the script task:

    public void Main()
    {
        try
        {

            //Your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
            Dts.Events.FireError(-1, "", ex.Message, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

It's always a challenge, especially with a deployed SSIS package, when it errors on a scrip task you don't necessarily get a clear indication as to why it's failing and you get a cryptic error message. The above code will capture what threw the exception and bubble back out to integration services what that was.

Tim Mylott
  • 2,553
  • 1
  • 5
  • 11
  • Upvoted this answer because it's a VERY useful technique to get more info about Script Task exceptions when you aren't running the package in debug mode (i.e. via BIDS/SSDT/VS). – digital.aaron Oct 04 '18 at 19:57
-1

You may want to make sure that the "Microsoft Access Database Engine 2010 Redistributable" driver is installed on the SSIS server. You can get it here.

Ken Garber
  • 1