SSIS package gives error after deployment SQL Server 2012

Question

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.

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.

What could be the problem?

Answer 1

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.

Answer 2

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.