Invoking .dtsx file via Excel - different behavior

  • Hello All,

    I am fairly new to SSDT/SSIS. The below is executed on a windows 10 machine. The SSDT version is 15.9.34

    I created a workflow where a flat file (csv) is imported to a SQL database table. Thereafter, a stored procedure kicks off and populates a second SQL database table. Finally, a message box pops up, stating the task is completed. This will be executed outside of SSDT, from Excel VBA using dtexec, as shown below.

    Dim Command As String

    Command = "dtexec /f ""C:\Users\.....\xxxxx.dtsx"""

     

    The problem is, when I hit start within SSDT, the execution goes as planned but when I invoke it the package via Excel, there is no pop up box stating the task is done. This is an important step, as it will allow the user to know when the SQL database table is populated. Any ideas why the message box fails to pop up?

    Thanks,

  • How are you displaying the popup? You need to display it from Excel, not from within the package.

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hi,

    I am displaying the message via Script Task, in SSDT, using the standard pop up window code,

    string message = "Task is completed.";

    string caption = "ATTENTION";

    DialogResult button = MessageBox.Show(message, caption, MessageBoxButtons.OK, MessageBoxIcon.Information);

    Dts.TaskResult = (int)ScriptResults.Success;

    This is occurs at the end of workflow, which is occurs successfully.

    The million dollar question is, how would I get Excel to know when the .dtsx package has completed?

    So far, I haven't been able to have Excel communicate with SSDT, other than kicking off a the .dtsx file.

    Thanks,

  • As packages usually run within the context of an unattended process, message boxes within them should be avoided (unless developing or debugging).

    I notice you mentioned users running this. For them to be able to run DTExec means that they will need some components installed – I'm not 100% sure here, but think that means SSDT with SSIS. Most companies would choose not to do that.

    There is another way. As long as you have the package deployed to SSISDB, you may execute it using a SQL Server stored procedure (which can, of course, accept runtime parameters). When the stored proc kicks off the package, you can choose to execute synchronously  (await completion) or asynchronously (fire and forget).

    So your Excel VBA code could kick off a proc, await the result and display a completion (or error) message to your users.

    The benefit of doing this is that users can execute packages without any need for additional tooling.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil,

    You provided a great idea that seems the best option for my next step. Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply