none
SSIS script task failing when deployed as project in SSIS 2012

    Question

  • Yes , it's the classic "my package works inside SSDT but dies when I deploy it". I  have a fairly good idea what the problem is. I have a line of code that looks in a directory. My guess is that the account that is used to run packages out of SSISDB doesn't have access to the directory in question. My question is, what exactly needs to be set properly in order for packages to run properly in the package deployment model.

    Error message: Exception has been thrown by the target of an invocation.

    Code:

    using System;
    using System.Data;
    using System.IO;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;

     public void Main()
            {
                String FilePath;

                FilePath = Dts.Variables["InitialDirectoryFullPath"].Value.ToString();

             

                if (Directory.GetFiles(FilePath).Length == 0)
                {
                    Dts.Variables["FilesExist"].Value = false;
                }
                else
                {
                    Dts.Variables["FilesExist"].Value = true;
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }

        
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
     


    • Edited by falcon00 Tuesday, August 05, 2014 2:29 PM
    Tuesday, August 05, 2014 1:39 AM

Answers

  • You need to give the account which is executing package the access to the directory. If its from SQL Agent job, it might be using SQL Agent service account by default. Then you've to give it required permissions

    Alternatively you can create a proxy account with required permissions and configure the job to use it

    http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

    Also you've not posted error message. Whatever I've suggested is by assuming that its a permission issue. If you can post error message we might be able to confirm on the actual cause of error.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by SSISJoostModerator Tuesday, August 05, 2014 6:20 AM
    • Marked as answer by falcon00 Tuesday, August 05, 2014 2:29 PM
    Tuesday, August 05, 2014 3:52 AM
  • Error message was right there after the first paragraph. It's not every helpful:

    "Error message: Exception has been thrown by the target of an invocation."

    That's all I got but after some googling that's what pointed me to the line of code trying to access the file.

    Thanks for your response. It actually DOES answer my question, but in the case of this bug it was something silly simple. The directory didn't exist on the test server. I didn't realize that once I deployed the package it would be looking for the directory on the test server instead of my local machine where I normally do testing. Silly me.

    • Marked as answer by falcon00 Tuesday, August 05, 2014 2:29 PM
    Tuesday, August 05, 2014 2:29 PM

All replies

  • You need to give the account which is executing package the access to the directory. If its from SQL Agent job, it might be using SQL Agent service account by default. Then you've to give it required permissions

    Alternatively you can create a proxy account with required permissions and configure the job to use it

    http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

    Also you've not posted error message. Whatever I've suggested is by assuming that its a permission issue. If you can post error message we might be able to confirm on the actual cause of error.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by SSISJoostModerator Tuesday, August 05, 2014 6:20 AM
    • Marked as answer by falcon00 Tuesday, August 05, 2014 2:29 PM
    Tuesday, August 05, 2014 3:52 AM
  • Error message was right there after the first paragraph. It's not every helpful:

    "Error message: Exception has been thrown by the target of an invocation."

    That's all I got but after some googling that's what pointed me to the line of code trying to access the file.

    Thanks for your response. It actually DOES answer my question, but in the case of this bug it was something silly simple. The directory didn't exist on the test server. I didn't realize that once I deployed the package it would be looking for the directory on the test server instead of my local machine where I normally do testing. Silly me.

    • Marked as answer by falcon00 Tuesday, August 05, 2014 2:29 PM
    Tuesday, August 05, 2014 2:29 PM