How can I run a SSIS Package using Script Task (C#)?

Question

What I want to do is run a SSIS Package using this command line:

/SQL "\"\[PACKAGE_NAME]\"" /SERVER [SERVER NAME] /X86 /CHECKPOINTING OFF /REPORTING E

This is my code until now:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;

namespace blablabla.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Variables 
            string targetServerName = Dts.Variables["SSIS_SERVER"].Value.ToString();
            string packageExecutionName = "package_folder" + "\"" + "package_name";

            ProcessStartInfo startInfo = new ProcessStartInfo();
            startInfo.WindowStyle = ProcessWindowStyle.Hidden;
            startInfo.UseShellExecute = false;
            startInfo.RedirectStandardOutput = true;
            startInfo.CreateNoWindow = true;
            startInfo.FileName = @"dtexec.exe";
            string arguments = @"/SQL " + "\"" + "\\" + packageExecutionName + "\"" + " / SERVER " + Dts.Variables["SSIS_SERVER"].Value.ToString() + " / X86 / CHECKPOINTING OFF / REPORTING E /CONSOLELOG M";

            Dts.Variables["SSIS_SERVER"].Value.ToString();

            // arguments to dtexec
            startInfo.Arguments = arguments;

            // begin execution
            Process proc = Process.Start(startInfo);
    
        }
    
    }
}

The script task runs with success but the package itself doesn't run, so the code must be wrong...

This is my very first time coding in C#, so apologies for any "trivial" error. I've searched on the internet but almost all of the cases it's using the specific folder where the package is saved and then run the package there, but what I need is running with this specific command line.

Thanks for your time.

Mark Wojciechowicz · Accepted Answer · 2022-06-22 19:51:59Z

I would guess that the package is never executing because the syntax for the arguments has extra spaces, like "/ X86" instead of "/X86".

If you check the error code, you can throw an exception if it is not 0. To see the details of that error, you'll need to look at StandardOutput because dtexec does not direct anything to StandardError:

public void Main()
        {
            //wrap the whole thing in a try..catch to report on errors
            try
            {
                // Variables 
                string targetServerName = Dts.Variables["SSIS_SERVER"].Value.ToString();
                string packageExecutionName = "package_folder" + "\"" + "package_name";

                ProcessStartInfo startInfo = new ProcessStartInfo();
                startInfo.WindowStyle = ProcessWindowStyle.Hidden;
                startInfo.UseShellExecute = false;
                startInfo.RedirectStandardOutput = true;
                startInfo.CreateNoWindow = true;
                startInfo.FileName = @"dtexec.exe";

                //tell the process to capture standard error
                startInfo.RedirectStandardOutput = true;
                string arguments = @"/SQL " + "\"" + "\\" + packageExecutionName + "\"" + " / SERVER " + Dts.Variables["SSIS_SERVER"].Value.ToString() + " / X86 / CHECKPOINTING OFF / REPORTING E /CONSOLELOG M";

                Dts.Variables["SSIS_SERVER"].Value.ToString();

                // arguments to dtexec
                startInfo.Arguments = arguments;

                // begin execution
                Process proc = Process.Start(startInfo);

                //capture standard output and wait for exit
                string error = proc.StandardOutput.ReadToEnd();
                proc.WaitForExit();

                //Check the exit code and throw an error
                if(proc.ExitCode != 0)
                {
                    throw new Exception(error.ToString());
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch(Exception e)
            {
                //write to the ssis log
                Dts.Events.FireError(1, "", e.Message, "", 0);
            }
        }

Thanks! It didn't solve the problem itself, but it definitely helped me to troubleshoot! Basically what was missing in my code was the direct path to the dtexec.exec file

Collectives™ on Stack Overflow

How can I run a SSIS Package using Script Task (C#)?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related