Search code examples
sql-serverssisssis-2016

SSIS package gives error after deployment SQL Server 2012

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?

Solution

  • 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.